<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>Chapter 5 Scrubbing Data | Data Science at the Command Line, 2e</title>
<meta name="author" content="Jeroen Janssens">
<meta name="description" content="Two chapters ago, in the first step of the OSEMN model for data science, we looked at obtaining data from a variety of sources. This chapter is all about the second step: scrubbing data. You see,...">
<meta name="generator" content="bookdown 0.24 with bs4_book()">
<meta property="og:title" content="Chapter 5 Scrubbing Data | Data Science at the Command Line, 2e">
<meta property="og:type" content="book">
<meta property="og:url" content="https://datascienceatthecommandline.com/chapter-5-scrubbing-data.html">
<meta property="og:image" content="https://datascienceatthecommandline.com/og.png">
<meta property="og:description" content="Two chapters ago, in the first step of the OSEMN model for data science, we looked at obtaining data from a variety of sources. This chapter is all about the second step: scrubbing data. You see,...">
<meta name="twitter:card" content="summary_large_image">
<meta name="twitter:title" content="Chapter 5 Scrubbing Data | Data Science at the Command Line, 2e">
<meta name="twitter:description" content="Two chapters ago, in the first step of the OSEMN model for data science, we looked at obtaining data from a variety of sources. This chapter is all about the second step: scrubbing data. You see,...">
<meta name="twitter:image" content="https://datascienceatthecommandline.com/twitter.png">
<!-- JS --><script src="https://cdnjs.cloudflare.com/ajax/libs/clipboard.js/2.0.6/clipboard.min.js" integrity="sha256-inc5kl9MA1hkeYUt+EC3BhlIgyp/2jDIyBLS6k3UxPI=" crossorigin="anonymous"></script><script src="https://cdnjs.cloudflare.com/ajax/libs/fuse.js/6.4.6/fuse.js" integrity="sha512-zv6Ywkjyktsohkbp9bb45V6tEMoWhzFzXis+LrMehmJZZSys19Yxf1dopHx7WzIKxr5tK2dVcYmaCk2uqdjF4A==" crossorigin="anonymous"></script><script src="https://kit.fontawesome.com/6ecbd6c532.js" crossorigin="anonymous"></script><script src="libs/header-attrs-2.9/header-attrs.js"></script><script src="libs/jquery-3.6.0/jquery-3.6.0.min.js"></script><meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link href="libs/bootstrap-4.6.0/bootstrap.min.css" rel="stylesheet">
<script src="libs/bootstrap-4.6.0/bootstrap.bundle.min.js"></script><link href="libs/_Source%20Sans%20Pro-0.4.0/font.css" rel="stylesheet">
<link href="https://fonts.googleapis.com/css2?family=Fira%20Mono:wght@400;600&amp;display=swap" rel="stylesheet">
<script src="libs/bs3compat-0.3.1/transition.js"></script><script src="libs/bs3compat-0.3.1/tabs.js"></script><script src="libs/bs3compat-0.3.1/bs3compat.js"></script><link href="libs/bs4_book-1.0.0/bs4_book.css" rel="stylesheet">
<script src="libs/bs4_book-1.0.0/bs4_book.js"></script><link rel="apple-touch-icon" sizes="180x180" href="/apple-touch-icon.png">
<link rel="icon" type="image/png" sizes="32x32" href="/favicon-32x32.png">
<link rel="icon" type="image/png" sizes="16x16" href="/favicon-16x16.png">
<link rel="manifest" href="/site.webmanifest">
<link rel="mask-icon" href="/safari-pinned-tab.svg" color="#d42d2d">
<meta name="apple-mobile-web-app-title" content="Data Science at the Command Line">
<meta name="application-name" content="Data Science at the Command Line">
<meta name="msapplication-TileColor" content="#b91d47">
<meta name="theme-color" content="#ffffff">
<script>
      (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
      (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
      m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
      })(window,document,'script','https://www.google-analytics.com/analytics.js','ga');
      ga('create', 'UA-43246574-3', 'auto');
      ga('send', 'pageview');
    </script><script src="https://cdnjs.cloudflare.com/ajax/libs/autocomplete.js/0.38.0/autocomplete.jquery.min.js" integrity="sha512-GU9ayf+66Xx2TmpxqJpliWbT5PiGYxpaG8rfnBEk1LL8l1KGkRShhngwdXK1UgqhAzWpZHSiYPc09/NwDQIGyg==" crossorigin="anonymous"></script><script src="https://cdnjs.cloudflare.com/ajax/libs/mark.js/8.11.1/mark.min.js" integrity="sha512-5CYOlHXGh6QpOFA/TeTylKLWfB3ftPsde7AnmhuitiTX4K5SqCLBeKro6sPS8ilsz1Q4NRx3v8Ko2IBiszzdww==" crossorigin="anonymous"></script><!-- CSS --><link rel="stylesheet" href="dsatcl2e.css">
</head>
<body data-spy="scroll" data-target="#toc">

<div class="container-fluid">
<div class="row">
  <header class="col-sm-12 col-lg-2 sidebar sidebar-book"><a class="sr-only sr-only-focusable" href="#content">Skip to main content</a>

    <div class="d-flex align-items-start justify-content-between">
      <img id="cover" class="d-none d-lg-block" src="images/cover-small.png"><h1 class="d-lg-none">
        <a href="index.html" title="">Data Science at the Command Line, 2e</a>
      </h1>
      <button class="btn btn-outline-primary d-lg-none ml-2 mt-1" type="button" data-toggle="collapse" data-target="#main-nav" aria-expanded="true" aria-controls="main-nav"><i class="fas fa-bars"></i><span class="sr-only">Show table of contents</span></button>
    </div>

    <div id="main-nav" class="collapse-lg">
      <form role="search">
        <input id="search" class="form-control" type="search" placeholder="Search" aria-label="Search">
</form>
      <nav aria-label="Table of contents"><h2>Table of contents</h2>
        <ul class="book-toc list-unstyled">
<li><a class="" href="index.html">Welcome</a></li>
<li><a class="" href="foreword.html">Foreword</a></li>
<li><a class="" href="preface.html">Preface</a></li>
<li><a class="" href="chapter-1-introduction.html"><span class="header-section-number">1</span> Introduction</a></li>
<li><a class="" href="chapter-2-getting-started.html"><span class="header-section-number">2</span> Getting Started</a></li>
<li><a class="" href="chapter-3-obtaining-data.html"><span class="header-section-number">3</span> Obtaining Data</a></li>
<li><a class="" href="chapter-4-creating-command-line-tools.html"><span class="header-section-number">4</span> Creating Command-line Tools</a></li>
<li><a class="active" href="chapter-5-scrubbing-data.html"><span class="header-section-number">5</span> Scrubbing Data</a></li>
<li><a class="" href="chapter-6-project-management-with-make.html"><span class="header-section-number">6</span> Project Management with Make</a></li>
<li><a class="" href="chapter-7-exploring-data.html"><span class="header-section-number">7</span> Exploring Data</a></li>
<li><a class="" href="chapter-8-parallel-pipelines.html"><span class="header-section-number">8</span> Parallel Pipelines</a></li>
<li><a class="" href="chapter-9-modeling-data.html"><span class="header-section-number">9</span> Modeling Data</a></li>
<li><a class="" href="chapter-10-polyglot-data-science.html"><span class="header-section-number">10</span> Polyglot Data Science</a></li>
<li><a class="" href="chapter-11-conclusion.html"><span class="header-section-number">11</span> Conclusion</a></li>
<li><a class="" href="list-of-command-line-tools.html">List of Command-Line Tools</a></li>
</ul>

        <div class="book-extra">
          <p><a id="book-repo" href="https://github.com/jeroenjanssens/data-science-at-the-command-line">View book repository <i class=""></i></a></p>
        </div>

        <div>
          <a id="course-signup" href="/#course">Embrace the Command Line</a>
        </div>
      </nav>
</div>
  </header><main class="col-sm-12 col-md-9 col-lg-7" id="content"><div id="chapter-5-scrubbing-data" class="section level1" number="5">
<h1>
<span class="header-section-number">5</span> Scrubbing Data<a class="anchor" aria-label="anchor" href="#chapter-5-scrubbing-data"><i class="fas fa-link"></i></a>
</h1>
<p>Two chapters ago, in the first step of the OSEMN model for data science, we looked at <em>obtaining</em> data from a variety of sources.
This chapter is all about the second step: <em>scrubbing</em> data.
You see, it’s quite rare that you can immediately continue with <em>exploring</em> or even <em>modeling</em> the data.
There’s a plethora of reasons why your data first needs some cleaning, or scrubbing.</p>
<p>For starters, the data might not be in the desired format.
For example, you may have obtained some JSON data from an API, but you need it to be in CSV format to create a visualization.
Other common formats include plain text, HTML, and XML.
Most command-line tools only work with one or two formats, so it’s important that you’re able to convert data from one format to another.</p>
<p>Once the data is in the desired format, there could still be issues like missing values, inconsistencies, weird characters, or unnecessary parts.
You can fix these by applying filters, replacing values, and combining multiple files.
The command line is especially well-suited for these kind of transformations, because there are many specialized tools available, most of which can handle large amounts of data.
In this chapter I’ll discuss classic tools such as <code>grep</code><span class="citation"><a class="footnote-ref" tabindex="0" data-toggle="popover" data-content='&lt;p&gt;&lt;a href="#ref-grep" role="doc-biblioref"&gt;Meyering, &lt;em&gt;&lt;span class="nocase"&gt;grep&lt;/span&gt; – Print Lines That Match Patterns&lt;/em&gt;&lt;/a&gt;.&lt;/p&gt;'><sup>60</sup></a></span> and <code>awk</code><span class="citation"><a class="footnote-ref" tabindex="0" data-toggle="popover" data-content='&lt;p&gt;&lt;a href="#ref-awk" role="doc-biblioref"&gt;Mike D. Brennan and Thomas E. Dickey, &lt;em&gt;&lt;span class="nocase"&gt;awk&lt;/span&gt; – Pattern Scanning and Text Processing Language&lt;/em&gt;, version 1.3.4, 2019, &lt;/a&gt;&lt;a href="https://invisible-island.net/mawk" role="doc-biblioref"&gt;https://invisible-island.net/mawk&lt;/a&gt;.&lt;/p&gt;'><sup>61</sup></a></span>, and newer tools such as <code>jq</code><span class="citation"><a class="footnote-ref" tabindex="0" data-toggle="popover" data-content='&lt;p&gt;&lt;a href="#ref-jq" role="doc-biblioref"&gt;Dolan, &lt;em&gt;&lt;span class="nocase"&gt;jq&lt;/span&gt; – Command-Line &lt;span&gt;JSON&lt;/span&gt; Processor&lt;/em&gt;&lt;/a&gt;.&lt;/p&gt;'><sup>62</sup></a></span> and <code>pup</code><span class="citation"><a class="footnote-ref" tabindex="0" data-toggle="popover" data-content='&lt;p&gt;&lt;a href="#ref-pup" role="doc-biblioref"&gt;Chiang, &lt;em&gt;&lt;span class="nocase"&gt;pup&lt;/span&gt; – Parsing HTML at the Command Line&lt;/em&gt;&lt;/a&gt;.&lt;/p&gt;'><sup>63</sup></a></span>.</p>
<p>Sometimes you can use the same command-line tool to perform several operations or multiple tools to perform the same operation.
This chapter is more structured like a cookbook, where the focus is on the problems or recipes, rather than diving deeply into the command-line tools themselves.</p>
<div id="overview-2" class="section level2" number="5.1">
<h2>
<span class="header-section-number">5.1</span> Overview<a class="anchor" aria-label="anchor" href="#overview-2"><i class="fas fa-link"></i></a>
</h2>
<!-- #TODO: SHOULD: Review the list below once the chapter is complete -->
<p>In this chapter, you’ll learn how to:</p>
<ul>
<li>Convert data from one format to another</li>
<li>Apply SQL queries directly to CSV</li>
<li>Filter lines</li>
<li>Extract and replace values</li>
<li>Split, merge, and extract columns</li>
<li>Combine multiple files</li>
</ul>
<p>This chapter starts with the following files:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">cd</span> <span style="text-decoration: underline">/data/ch05</span>
 
<span style="font-weight: bold">$</span> <span style="color: #5f8700">l</span>
total 200K
-rw-r--r-- 1 dst dst 164K Dec 14 11:47 alice.txt
-rw-r--r-- 1 dst dst 4.5K Dec 14 11:47 iris.csv
-rw-r--r-- 1 dst dst  179 Dec 14 11:47 irismeta.csv
-rw-r--r-- 1 dst dst  160 Dec 14 11:47 names-comma.csv
-rw-r--r-- 1 dst dst  129 Dec 14 11:47 names.csv
-rw-r--r-- 1 dst dst 7.8K Dec 14 11:47 tips.csv
-rw-r--r-- 1 dst dst 5.1K Dec 14 11:47 users.json</pre>
<p>The instructions to get these files are in <a href="chapter-2-getting-started.html#chapter-2-getting-started">Chapter 2</a>.
Any other files are either downloaded or generated using command-line tools.</p>
<p>Before I dive into the actual transformations, I’d like to demonstrate their ubiquity when working at the command line.</p>
</div>
<div id="transformations-transformations-everywhere" class="section level2" number="5.2">
<h2>
<span class="header-section-number">5.2</span> Transformations, Transformations Everywhere<a class="anchor" aria-label="anchor" href="#transformations-transformations-everywhere"><i class="fas fa-link"></i></a>
</h2>
<p>In <a href="chapter-1-introduction.html#chapter-1-introduction">Chapter 1</a> I mentioned that, in practice, the steps of the OSEMN model will rarely be followed linearly.
In this vein, although scrubbing is the second step of the OSEMN model, I want you to know that it’s not <em>just</em> the obtained data that needs scrubbing.
The transformations that you’ll learn in this chapter can be useful at any part of your pipeline and at any step of the OSEMN model.
Generally, if one command line tool generates output that can be used immediately by the next tool, you can chain the two tools together by using the pipe operator (<code>|</code>).
Otherwise, a transformation needs to be applied to the data first by inserting an intermediate tool into the pipeline.</p>
<p>Let me walk you through an example to make this more concrete.
Imagine that you have obtained the first 100 items of a <em>fizzbuzz</em> sequence (cf. <a href="chapter-4-creating-command-line-tools.html#chapter-4-creating-command-line-tools">Chapter 4</a>) and that you’d like to visualize how often the words <em>fizz</em>, <em>buzz</em>, and <em>fizzbuzz</em> appear using a bar chart.
Don’t worry if this example uses tools that you might not be familiar with yet, they’ll all be covered in more detail later.</p>
<p>First you obtain the data by generating the sequence and write it to <em>fb.seq</em>:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">seq</span> 100 |
<span style="font-weight: bold">&gt;</span> <span style="color: #5f8700">/data/ch04/fizzbuzz.py</span> | <span class="callout">➊</span>
<span style="font-weight: bold">&gt;</span> <span style="color: #5f8700">tee</span> fb.seq | <span style="color: #5f8700">trim</span>
1
2
fizz
4
buzz
fizz
7
8
fizz
buzz
… with 90 more lines</pre>
<p><span class="callout">➊</span> The custom tool <code>fizzbuzz.py</code> comes from <a href="chapter-4-creating-command-line-tools.html#chapter-4-creating-command-line-tools">Chapter 4</a>.</p>
<p>Then you use <code>grep</code> to keep the lines that match the pattern <em>fizz</em> or <em>buzz</em> and count how often each word appears using <code>sort</code> and <code>uniq</code><span class="citation"><a class="footnote-ref" tabindex="0" data-toggle="popover" data-content='&lt;p&gt;&lt;a href="#ref-uniq" role="doc-biblioref"&gt;Stallman and MacKenzie, &lt;em&gt;&lt;span class="nocase"&gt;uniq&lt;/span&gt; – Report or Omit Repeated Lines&lt;/em&gt;&lt;/a&gt;.&lt;/p&gt;'><sup>64</sup></a></span>:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">grep</span> -E <span style="color: #af8700">"fizz|buzz"</span> <span style="text-decoration: underline">fb.seq</span> | <span style="font-weight: bold"></span><span style="font-weight: bold; color: #262626"><span class="callout">➊</span></span>
<span style="font-weight: bold">&gt;</span> <span style="color: #5f8700">sort</span> | <span style="color: #5f8700">uniq</span> -c | <span style="color: #5f8700">sort</span> -nr <span style="color: #af8700">&gt;</span> fb.cnt <span style="font-weight: bold"></span><span style="font-weight: bold; color: #262626"><span class="callout">➋</span></span>
 
<span style="font-weight: bold">$</span> <span style="color: #5f8700">bat</span> -A <span style="text-decoration: underline">fb.cnt</span>
───────┬────────────────────────────────────────────────────────────────────────
       │ File: <span style="font-weight: bold">fb.cnt</span>
───────┼────────────────────────────────────────────────────────────────────────
   1   │ <span style="color: #00afaf">·····</span>27<span style="color: #00afaf">·</span>fizz<span style="color: #af005f">␊</span>
   2   │ <span style="color: #00afaf">·····</span>14<span style="color: #00afaf">·</span>buzz<span style="color: #af005f">␊</span>
   3   │ <span style="color: #00afaf">······</span>6<span style="color: #00afaf">·</span>fizzbuzz<span style="color: #af005f">␊</span>
───────┴────────────────────────────────────────────────────────────────────────</pre>
<p><span class="callout">➊</span> This regular expression also matches <em>fizzbuzz</em>.
<br><span class="callout">➋</span> Using <code>sort</code> and <code>uniq</code> this way is a common way to count lines and sort them in descending order. It’s the <code>-c</code> option that adds the counts.</p>
<p>Note that <code>sort</code> is used twice: first because <code>uniq</code> assumes its input data to be sorted and second to sort the counts numerically.
In a way, this is an intermediate transformation, albeit a subtle one.</p>
<p>The next step would be to visualize the counts using <code>rush</code><span class="citation"><a class="footnote-ref" tabindex="0" data-toggle="popover" data-content='&lt;p&gt;&lt;a href="#ref-rush" role="doc-biblioref"&gt;Janssens, &lt;em&gt;&lt;span class="nocase"&gt;rush&lt;/span&gt; – &lt;span&gt;R&lt;/span&gt; One-Liners from the Shell&lt;/em&gt;&lt;/a&gt;.&lt;/p&gt;'><sup>65</sup></a></span>.
However, since <code>rush</code> expects the input data to be in CSV format, this requires a less subtle transformation first.
<code>awk</code> can add a header, flip the two fields, and insert commas in a single incantation:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">fb.cnt</span> <span style="color: #5f8700">awk</span> <span style="color: #af8700">'BEGIN { print "value,count" } { print $2","$1 }'</span> <span style="color: #af8700">&gt;</span> fb.csv
 
<span style="font-weight: bold">$</span> <span style="color: #5f8700">bat</span> <span style="text-decoration: underline">fb.csv</span>
───────┬────────────────────────────────────────────────────────────────────────
       │ File: <span style="font-weight: bold">fb.csv</span>
───────┼────────────────────────────────────────────────────────────────────────
   1   │ <span style="color: #af005f">value,count
</span>   2   │ <span style="color: #af005f">fizz,</span><span style="color: #af8700">27</span>
   3   │ <span style="color: #af005f">buzz,</span><span style="color: #af8700">14</span>
   4   │ <span style="color: #af005f">fizzbuzz,</span><span style="color: #af8700">6</span>
───────┴────────────────────────────────────────────────────────────────────────
 
<span style="font-weight: bold">$</span> <span style="color: #5f8700">csvlook</span> <span style="text-decoration: underline">fb.csv</span>
│ value    │ count │
├──────────┼───────┤
│ fizz     │    27 │
│ buzz     │    14 │
│ fizzbuzz │     6 │</pre>
<p>Now you’re ready to use <code>rush</code> to create a bar chart.
See Figure <a href="chapter-5-scrubbing-data.html#fig:fb-image">5.1</a> for the result.
(I’ll cover this syntax of <code>rush</code> in detail in <a href="chapter-7-exploring-data.html#chapter-7-exploring-data">Chapter 7</a>.)</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">rush</span> plot -x value -y count --geom col --height 2 <span style="text-decoration: underline">fb.csv</span> <span style="color: #af8700">&gt;</span> fb.png
 
<span style="font-weight: bold">$</span> <span style="color: #5f8700">display</span> <span style="text-decoration: underline">fb.png</span></pre>
<div class="figure" style="text-align: center">
<span style="display:block;" id="fig:fb-image"></span>
<img src="images/fb.png" alt="Counting fizz, buzz, and fizzbuzz" width="90%"><p class="caption">
Figure 5.1: Counting fizz, buzz, and fizzbuzz
</p>
</div>
<p>Although this example is a bit contrived, it reveals a pattern that is common when working at the command line.
The key tools, such as the ones that obtain data, create a visualization, or train a model, often require intermediate transformations in order to be chained into a pipeline.
In that sense, writing a pipeline is like solving a puzzle, where the key pieces often require helper pieces to fit.</p>
<p>Now that you’ve seen the importance of scrubbing data, you’re ready to learn about some actual transformations.</p>
</div>
<div id="plain-text" class="section level2" number="5.3">
<h2>
<span class="header-section-number">5.3</span> Plain Text<a class="anchor" aria-label="anchor" href="#plain-text"><i class="fas fa-link"></i></a>
</h2>
<p>Formally speaking, <em>plain text</em> refers to a sequence of human-readable characters and optionally, some specific types of control characters such as tabs and newlines<span class="citation"><a class="footnote-ref" tabindex="0" data-toggle="popover" data-content='&lt;p&gt;&lt;a href="#ref-plaintext" role="doc-biblioref"&gt;The Linux Information Project, &lt;span&gt;“Plain Text Definition,”&lt;/span&gt; 2007, &lt;/a&gt;&lt;a href="http://www.linfo.org/plain_text.html" role="doc-biblioref"&gt;http://www.linfo.org/plain_text.html&lt;/a&gt;.&lt;/p&gt;'><sup>66</sup></a></span>.
Examples are logs, e-books, emails, and source code.
Plain text has many benefits over binary data<span class="citation"><a class="footnote-ref" tabindex="0" data-toggle="popover" data-content='&lt;p&gt;&lt;a href="#ref-pragmaticprogrammer" role="doc-biblioref"&gt;Andrew Hunt and David Thomas, &lt;em&gt;The Pragmatic Programmer&lt;/em&gt; (Addison-Wesley, 1999)&lt;/a&gt;.&lt;/p&gt;'><sup>67</sup></a></span>, including:</p>
<ul>
<li>It can be opened, edited, and saved using any text editor</li>
<li>It’s self-describing and independent of the application that created it</li>
<li>It will outlive other forms of data, because no additional knowledge or applications are required to process it</li>
</ul>
<p>But most importantly, the Unix philosophy considers plain text to be the universal interface between command-line tools<span class="citation"><a class="footnote-ref" tabindex="0" data-toggle="popover" data-content='&lt;p&gt;&lt;a href="#ref-raymond2003art" role="doc-biblioref"&gt;Raymond, &lt;em&gt;The Art of Unix Programming&lt;/em&gt;&lt;/a&gt;.&lt;/p&gt;'><sup>68</sup></a></span>.
Meaning, most tools accept plain text as input and produce plain text as output.</p>
<p>That’s reason enough for me to start with plain text.
The other formats that I discuss in this chapter, CSV, JSON, XML, and HTML are indeed also plain text.
For now, I assume that the plain text has no clear tabular structure (like CSV does) or nested structure (like JSON, XML, and HTML do).
Later in this chapter, I’ll introduce some tools that are specifically designed for working with these formats.</p>
<!-- #TODO: SHOULD: What to do with this part? -->
<!-- Although the tools in this section can also be applied to these other formats (because they're still text), -->
<!-- keep in mind that the tools treat the data as plain text, and don't interpret the tabular or nested structure. -->
<!-- Sometimes you can get away with this,  -->
<div id="filtering-lines" class="section level3" number="5.3.1">
<h3>
<span class="header-section-number">5.3.1</span> Filtering Lines<a class="anchor" aria-label="anchor" href="#filtering-lines"><i class="fas fa-link"></i></a>
</h3>
<p>The first scrubbing operation is filtering lines.
This means that from the input data, each line will be evaluated whether it will be kept or discarded.</p>
<div id="based-on-location" class="section level4" number="5.3.1.1">
<h4>
<span class="header-section-number">5.3.1.1</span> Based on Location<a class="anchor" aria-label="anchor" href="#based-on-location"><i class="fas fa-link"></i></a>
</h4>
<p>The most straightforward way to filter lines is based on their location.
This may be useful when you want to inspect, say, the top 10 lines of a file, or when you extract a specific row from the output of another command-line tool.
To illustrate how to filter based on location, let’s create a dummy file that contains 10 lines:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">seq</span> -f <span style="color: #af8700">"Line %g"</span> 10 | <span style="color: #5f8700">tee</span> lines
Line 1
Line 2
Line 3
Line 4
Line 5
Line 6
Line 7
Line 8
Line 9
Line 10</pre>
<p>You can print the first 3 lines using either <code>head</code><span class="citation"><a class="footnote-ref" tabindex="0" data-toggle="popover" data-content='&lt;p&gt;&lt;a href="#ref-head" role="doc-biblioref"&gt;MacKenzie and Meyering, &lt;em&gt;&lt;span class="nocase"&gt;head&lt;/span&gt; – Output the First Part of Files&lt;/em&gt;&lt;/a&gt;.&lt;/p&gt;'><sup>69</sup></a></span>, <code>sed</code><span class="citation"><a class="footnote-ref" tabindex="0" data-toggle="popover" data-content='&lt;p&gt;&lt;a href="#ref-sed" role="doc-biblioref"&gt;Jay Fenlason et al., &lt;em&gt;&lt;span class="nocase"&gt;sed&lt;/span&gt; – Stream Editor for Filtering and Transforming Text&lt;/em&gt;, version 4.7, 2018, &lt;/a&gt;&lt;a href="https://www.gnu.org/software/sed" role="doc-biblioref"&gt;https://www.gnu.org/software/sed&lt;/a&gt;.&lt;/p&gt;'><sup>70</sup></a></span>, or <code>awk</code>:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">lines</span> <span style="color: #5f8700">head</span> -n 3
Line 1
Line 2
Line 3
 
<span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">lines</span> <span style="color: #5f8700">sed</span> -n <span style="color: #af8700">'1,3p'</span>
Line 1
Line 2
Line 3
 
<span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">lines</span> <span style="color: #5f8700">awk</span> <span style="color: #af8700">'NR &lt;= 3'</span> <span class="callout">➊</span>
Line 1
Line 2
Line 3</pre>
<p><span class="callout">➊</span> In <code>awk</code>, <em>NR</em> refers to the total number of input records seen so far.</p>
<p>Similarly, you can print the last 3 lines using <code>tail</code><span class="citation"><a class="footnote-ref" tabindex="0" data-toggle="popover" data-content='&lt;p&gt;&lt;a href="#ref-tail" role="doc-biblioref"&gt;Paul Rubin et al., &lt;em&gt;&lt;span class="nocase"&gt;tail&lt;/span&gt; – Output the Last Part of Files&lt;/em&gt;, version 8.30, 2019, &lt;/a&gt;&lt;a href="https://www.gnu.org/software/coreutils" role="doc-biblioref"&gt;https://www.gnu.org/software/coreutils&lt;/a&gt;.&lt;/p&gt;'><sup>71</sup></a></span>:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">lines</span> <span style="color: #5f8700">tail</span> -n 3
Line 8
Line 9
Line 10</pre>
<p>You can also you use <code>sed</code> and <code>awk</code> for this, but <code>tail</code> is much faster.
Removing the first 3 lines goes as follows:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">lines</span> <span style="color: #5f8700">tail</span> -n +4
Line 4
Line 5
Line 6
Line 7
Line 8
Line 9
Line 10
 
<span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">lines</span> <span style="color: #5f8700">sed</span> <span style="color: #af8700">'1,3d'
</span>Line 4
Line 5
Line 6
Line 7
Line 8
Line 9
Line 10
 
<span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">lines</span> <span style="color: #5f8700">sed</span> -n <span style="color: #af8700">'1,3!p'</span>
Line 4
Line 5
Line 6
Line 7
Line 8
Line 9
Line 10</pre>
<p>Notice that with <code>tail</code> you have to specify the number of lines plus one.
Think of it as the line from which you want to start printing.
Removing the last 3 lines can be done with <code>head</code>:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">lines</span> <span style="color: #5f8700">head</span> -n -3
Line 1
Line 2
Line 3
Line 4
Line 5
Line 6
Line 7</pre>
<p>You can print specific lines using a either <code>sed</code>, <code>awk</code>, or a combination of <code>head</code> and <code>tail</code>.
Here I print lines 4, 5, and 6:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">lines</span> <span style="color: #5f8700">sed</span> -n <span style="color: #af8700">'4,6p'</span>
Line 4
Line 5
Line 6
 
<span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">lines</span> <span style="color: #5f8700">awk</span> <span style="color: #af8700">'(NR&gt;=4) &amp;&amp; (NR&lt;=6)'</span>
Line 4
Line 5
Line 6
 
<span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">lines</span> <span style="color: #5f8700">head</span> -n 6 | <span style="color: #5f8700">tail</span> -n 3
Line 4
Line 5
Line 6</pre>
<p>You can print odd lines with <code>sed</code> by specifying a start and a step, or with <code>awk</code> by using the modulo operator:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">lines</span> <span style="color: #5f8700">sed</span> -n <span style="color: #af8700">'1~2p'</span>
Line 1
Line 3
Line 5
Line 7
Line 9
 
<span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">lines</span> <span style="color: #5f8700">awk</span> <span style="color: #af8700">'NR%2'
</span>Line 1
Line 3
Line 5
Line 7
Line 9</pre>
<p>Printing even lines works in a similar manner:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">lines</span> <span style="color: #5f8700">sed</span> -n <span style="color: #af8700">'0~2p'</span>
Line 2
Line 4
Line 6
Line 8
Line 10
 
<span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">lines</span> <span style="color: #5f8700">awk</span> <span style="color: #af8700">'(NR+1)%2'</span>
Line 2
Line 4
Line 6
Line 8
Line 10</pre>
<!-- #TODO: SHOULD: Mention somewhere in the book that Linux doesn't care about file extensions -->

<div class="rmdnote">
Many of these examples start with the smaller-than sign (<code>&lt;</code>) followed by the filename.
I do this because this allows me to read the pipeline from left to right.
Please know that this is my own preference.
You can also use <code>cat</code> to pipe the contents of a file.
Also, many command-line tools also accept the filename as an argument.
</div>
</div>
<div id="based-on-a-pattern" class="section level4" number="5.3.1.2">
<h4>
<span class="header-section-number">5.3.1.2</span> Based on a Pattern<a class="anchor" aria-label="anchor" href="#based-on-a-pattern"><i class="fas fa-link"></i></a>
</h4>
<p>Sometimes you want to keep or discard lines based on their contents.
With <code>grep</code>, the canonical command-line tool for filtering lines, you can print every line that matches a certain pattern or regular expression.
For example, to extract all the chapter headings from <em>Alice’s Adventures in Wonderland</em>:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">alice.txt</span> <span style="color: #5f8700">grep</span> -i chapter <span class="callout">➊</span>
CHAPTER I. Down the Rabbit-Hole
CHAPTER II. The Pool of Tears
CHAPTER III. A Caucus-Race and a Long Tale
CHAPTER IV. The Rabbit Sends in a Little Bill
CHAPTER V. Advice from a Caterpillar
CHAPTER VI. Pig and Pepper
CHAPTER VII. A Mad Tea-Party
CHAPTER VIII. The Queen's Croquet-Ground
CHAPTER IX. The Mock Turtle's Story
CHAPTER X. The Lobster Quadrille
CHAPTER XI. Who Stole the Tarts?
CHAPTER XII. Alice's Evidence</pre>
<p><span class="callout">➊</span> The <code>-i</code> options specifies that the matching should be case-insensitive.</p>
<p>You can also specify a regular expression.
For example, if you only wanted to print the headings that start with <em>The</em>:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">alice.txt</span> <span style="color: #5f8700">grep</span> -E <span style="color: #af8700">'^CHAPTER (.*)\. The'</span>
CHAPTER II. The Pool of Tears
CHAPTER IV. The Rabbit Sends in a Little Bill
CHAPTER VIII. The Queen's Croquet-Ground
CHAPTER IX. The Mock Turtle's Story
CHAPTER X. The Lobster Quadrille</pre>
<p>Note that you have to specify the <code>-E</code> option in order to enable regular expressions.
Otherwise, <code>grep</code> interprets the pattern as a literal string which most likely results in no matches at all:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">alice.txt</span> <span style="color: #5f8700">grep</span> <span style="color: #af8700">'^CHAPTER (.*)\. The'</span></pre>
<p>With the <code>-v</code> option you invert the matches, so that <code>grep</code> prints the lines which <em>don’t</em> match the pattern.
The regular expression below matches lines that contain white space, only.
So with the inverse, and using <code>wc -l</code>, you can count the number of non-empty lines:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">alice.txt</span> <span style="color: #5f8700">grep</span> -Ev <span style="color: #af8700">'^\s$'</span> | <span style="color: #5f8700">wc</span> -l
2790</pre>
</div>
<div id="based-on-randomness" class="section level4" number="5.3.1.3">
<h4>
<span class="header-section-number">5.3.1.3</span> Based on Randomness<a class="anchor" aria-label="anchor" href="#based-on-randomness"><i class="fas fa-link"></i></a>
</h4>
<p>When you’re in the process of formulating your data pipeline and you have a lot of data, then debugging your pipeline can be cumbersome.
In that case, generating a smaller sample from the data might be useful.
This is where <code>sample</code><span class="citation"><a class="footnote-ref" tabindex="0" data-toggle="popover" data-content='&lt;p&gt;&lt;a href="#ref-sample" role="doc-biblioref"&gt;Jeroen Janssens, &lt;em&gt;&lt;span class="nocase"&gt;sample&lt;/span&gt; – Filter Lines from Standard Input According to Some Probability, with a Given Delay, and for a Certain Duration&lt;/em&gt;, version 0.2.4, 2021, &lt;/a&gt;&lt;a href="https://github.com/jeroenjanssens/sample" role="doc-biblioref"&gt;https://github.com/jeroenjanssens/sample&lt;/a&gt;.&lt;/p&gt;'><sup>72</sup></a></span> comes in handy.
The main purpose of <code>sample</code> is to get a subset of the data by outputting only a certain percentage of the input on a line-by-line basis.</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">seq</span> -f <span style="color: #af8700">"Line %g"</span> 1000 | <span style="color: #5f8700">sample</span> -r 1%
Line 45
Line 223
Line 355
Line 369
Line 438
Line 807
Line 813</pre>
<p>Here, every input line has a one percent chance of being printed.
This percentage can also be specified as a fraction (namely <code>1/100</code>) or as a probability (namely <code>0.01</code>).</p>
<p><code>sample</code> has two other purposes, which can be useful when you’re debugging your pipeline.
First, it’s possible to add some delay to the output.
This comes in handy when the input is a constant stream (for example, the Wikipedia stream we saw in <a href="chapter-3-obtaining-data.html#chapter-3-obtaining-data">Chapter 3</a>), and the data comes in too fast to see what’s going on.
Secondly, you can put a timer on <code>sample</code>, so that you don’t have to kill the ongoing process manually.
For example, to add a 1 second delay between each line being printed and to only run for 5 seconds, you would type:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">seq</span> -f <span style="color: #af8700">"Line %g"</span> 1000 | <span style="color: #5f8700">sample</span> -r 1% -d 1000 -s 5 | <span style="color: #5f8700">ts</span> <span class="callout">➊</span>
Dec 14 11:48:10 Line 58
Dec 14 11:48:11 Line 60
Dec 14 11:48:12 Line 230
Dec 14 11:48:13 Line 250
Dec 14 11:48:14 Line 785
Dec 14 11:48:15 Line 786</pre>
<p><span class="callout">➊</span> The tool <code>ts</code><span class="citation"><a class="footnote-ref" tabindex="0" data-toggle="popover" data-content='&lt;p&gt;&lt;a href="#ref-ts" role="doc-biblioref"&gt;Joey Hess, &lt;em&gt;&lt;span class="nocase"&gt;ts&lt;/span&gt; – Timestamp Input&lt;/em&gt;, version 0.65, 2021, &lt;/a&gt;&lt;a href="https://joeyh.name/code/moreutils" role="doc-biblioref"&gt;https://joeyh.name/code/moreutils&lt;/a&gt;.&lt;/p&gt;'><sup>73</sup></a></span> adds a timestamp in front of each line.</p>
<p>In order to prevent unnecessary computation, try to put <code>sample</code> as early as possible in your pipeline.
In fact, this argument holds for any command-line tool that reduces data, like <code>head</code> and <code>tail</code>.
Once you’re confident your pipeline works, you take it out of the pipeline.</p>
</div>
</div>
<div id="extracting-values" class="section level3" number="5.3.2">
<h3>
<span class="header-section-number">5.3.2</span> Extracting Values<a class="anchor" aria-label="anchor" href="#extracting-values"><i class="fas fa-link"></i></a>
</h3>
<p>To extract the actual chapter headings from our example earlier, you can take a simple approach by piping the output of <code>grep</code> to <code>cut</code>:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">grep</span> -i chapter <span style="text-decoration: underline">alice.txt</span> | <span style="color: #5f8700">cut</span> -d <span style="color: #af8700">' '</span> -f 3-
Down the Rabbit-Hole
The Pool of Tears
A Caucus-Race and a Long Tale
The Rabbit Sends in a Little Bill
Advice from a Caterpillar
Pig and Pepper
A Mad Tea-Party
The Queen's Croquet-Ground
The Mock Turtle's Story
The Lobster Quadrille
Who Stole the Tarts?
Alice's Evidence</pre>
<p>Here, each line that’s passed to <code>cut</code> is being split on spaces into fields, and then the third field to the last field is being printed.
The total number of fields can be different per input line.
With <code>sed</code> you can accomplish the same task in a much more complex manner:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">sed</span> -rn <span style="color: #af8700">'s/^CHAPTER ([IVXLCDM]{1,})\. (.*)$/\2/p'</span> <span style="text-decoration: underline">alice.txt</span> | <span style="color: #5f8700">trim</span> 3
Down the Rabbit-Hole
The Pool of Tears
A Caucus-Race and a Long Tale
… with 9 more lines</pre>
<p>(Since the output is the same it’s trimmed to three lines.) This approach uses a regular expression and a back reference.
Here, <code>sed</code> also takes over the work done by <code>grep</code>.
I only recommend using such a complicated approach when a simpler one would not work.
For example, if <em>chapter</em> was ever part of the text itself and not just used to indicate the start of a new chapter.
Of course there are many levels of complexity which would have worked around this, but this is to illustrate an extremely strict approach.
In practice, the challenge is to come up with a pipeline that strikes a good balance between complexity and flexibility.</p>
<p>It’s worth noting that <code>cut</code> can also split on characters positions.
This is useful for when you want to extract (or remove) the same set of characters per input line:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">grep</span> -i chapter <span style="text-decoration: underline">alice.txt</span> | <span style="color: #5f8700">cut</span> -c 9-
I. Down the Rabbit-Hole
II. The Pool of Tears
III. A Caucus-Race and a Long Tale
IV. The Rabbit Sends in a Little Bill
V. Advice from a Caterpillar
VI. Pig and Pepper
VII. A Mad Tea-Party
VIII. The Queen's Croquet-Ground
IX. The Mock Turtle's Story
X. The Lobster Quadrille
XI. Who Stole the Tarts?
XII. Alice's Evidence</pre>
<p><code>grep</code> has a great feature that outputs every match onto a separate line using the <code>-o</code> option:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">alice.txt</span> <span style="color: #5f8700">grep</span> -oE <span style="color: #af8700">'\w{2,}'</span> | <span style="color: #5f8700">trim</span>
Project
Gutenberg
Alice
Adventures
in
Wonderland
by
Lewis
Carroll
This
… with 28615 more lines</pre>
<p>But what if you wanted to create a dataset of all the words that start with an <em>a</em> and end with an <em>e</em>?
Well, of course there’s a pipeline for that too:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">alice.txt</span> <span style="color: #5f8700">tr</span> <span style="color: #af8700">'[:upper:]'</span> <span style="color: #af8700">'[:lower:]'</span> | <span class="callout">➊</span>
<span style="font-weight: bold">&gt;</span> <span style="color: #5f8700">grep</span> -oE <span style="color: #af8700">'\w{2,}'</span> |
<span style="font-weight: bold">&gt;</span> <span style="color: #5f8700">grep</span> -E <span style="color: #af8700">'^a.*e$'</span> |
<span style="font-weight: bold">&gt;</span> <span style="color: #5f8700">sort</span> | <span style="color: #5f8700">uniq</span> | <span style="color: #5f8700">sort</span> -nr | <span style="color: #5f8700">trim</span>
available
ate
assistance
askance
arise
argue
are
archive
applicable
apple
… with 25 more lines</pre>
<p><span class="callout">➊</span> Here I use <code>tr</code> to make the text lowercase. We’ll have a closer look at <code>tr</code> in the next section.</p>
<p>The two <code>grep</code> commands might have been combined into one, but in this case I decided it would be easier to reuse and adapt the previous pipeline.
There’s no shame in being pragmatic in order to get the job done!</p>
</div>
<div id="replacing-and-deleting-values" class="section level3" number="5.3.3">
<h3>
<span class="header-section-number">5.3.3</span> Replacing and Deleting Values<a class="anchor" aria-label="anchor" href="#replacing-and-deleting-values"><i class="fas fa-link"></i></a>
</h3>
<p>You can use the command-line tool <code>tr</code><span class="citation"><a class="footnote-ref" tabindex="0" data-toggle="popover" data-content='&lt;p&gt;&lt;a href="#ref-tr" role="doc-biblioref"&gt;Meyering, &lt;em&gt;&lt;span class="nocase"&gt;tr&lt;/span&gt; – Translate or Delete Characters&lt;/em&gt;&lt;/a&gt;.&lt;/p&gt;'><sup>74</sup></a></span>, which stands for <em>translate</em>, to replace or delete individual characters.
For example, spaces can be replaced by underscores as follows:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">echo</span> <span style="color: #af8700">'hello world!'</span> | <span style="color: #5f8700">tr</span> <span style="color: #af8700">' '</span> <span style="color: #af8700">'_'</span>
hello_world!</pre>
<p>If more than one character needs to be replaced, then you can combine that:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">echo</span> <span style="color: #af8700">'hello world!'</span> | <span style="color: #5f8700">tr</span> <span style="color: #af8700">' !'</span> <span style="color: #af8700">'_?'</span>
hello_world?</pre>
<p><code>tr</code> can also be used to delete individual characters by specifying the argument <code>-d</code>:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">echo</span> <span style="color: #af8700">'hello world!'</span> | <span style="color: #5f8700">tr</span> -d <span style="color: #af8700">' !'</span>
helloworld
 
<span style="font-weight: bold">$</span> <span style="color: #5f8700">echo</span> <span style="color: #af8700">'hello world!'</span> | <span style="color: #5f8700">tr</span> -d -c <span style="color: #af8700">'[a-z]'
</span>helloworld<span style="font-weight: bold; font-style: italic">%</span></pre>
<p>In this case, these two commands accomplish the same thing.
The second command, however, uses two additional features:
It specifies a <em>range</em> of characters (all lowercase letters) using the square brackets and the dash (<code>[-]</code>), and the <code>-c</code> option indicates that complement of that should be used.
In other words, this command only keeps lowercase letters.
You can even use <code>tr</code> to convert text to uppercase:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">echo</span> <span style="color: #af8700">'hello world!'</span> | <span style="color: #5f8700">tr</span> <span style="color: #af8700">'[a-z]'</span> <span style="color: #af8700">'[A-Z]'</span>
HELLO WORLD!
 
<span style="font-weight: bold">$</span> <span style="color: #5f8700">echo</span> <span style="color: #af8700">'hello world!'</span> | <span style="color: #5f8700">tr</span> <span style="color: #af8700">'[:lower:]'</span> <span style="color: #af8700">'[:upper:]'</span>
HELLO WORLD!</pre>
<p>However, if you need to translate non-ASCII characters, then <code>tr</code> may not work because it operates on single-byte characters only. In those cases you should use <code>sed</code> instead:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">echo</span> <span style="color: #af8700">'hello world!'</span> | <span style="color: #5f8700">tr</span> <span style="color: #af8700">'[a-z]'</span> <span style="color: #af8700">'[A-Z]'</span>
HELLO WORLD!
 
<span style="font-weight: bold">$</span> <span style="color: #5f8700">echo</span> <span style="color: #af8700">'hallo wêreld!'</span> | <span style="color: #5f8700">tr</span> <span style="color: #af8700">'[a-z]'</span> <span style="color: #af8700">'[A-Z]'</span>
HALLO WêRELD!
 
<span style="font-weight: bold">$</span> <span style="color: #5f8700">echo</span> <span style="color: #af8700">'hallo wêreld!'</span> | <span style="color: #5f8700">tr</span> <span style="color: #af8700">'[:lower:]'</span> <span style="color: #af8700">'[:upper:]'</span>
HALLO WêRELD!
 
<span style="font-weight: bold">$</span> <span style="color: #5f8700">echo</span> <span style="color: #af8700">'hallo wêreld!'</span> | <span style="color: #5f8700">sed</span> <span style="color: #af8700">'s/[[:lower:]]*/\U&amp;/g'</span>
HALLO WÊRELD!
 
<span style="font-weight: bold">$</span> <span style="color: #5f8700">echo</span> <span style="color: #af8700">'helló világ'</span> | <span style="color: #5f8700">tr</span> <span style="color: #af8700">'[:lower:]'</span> <span style="color: #af8700">'[:upper:]'</span>
HELLó VILáG
 
<span style="font-weight: bold">$</span> <span style="color: #5f8700">echo</span> <span style="color: #af8700">'helló világ'</span> | <span style="color: #5f8700">sed</span> <span style="color: #af8700">'s/[[:lower:]]*/\U&amp;/g'</span>
HELLÓ VILÁG</pre>
<!-- #TODO: SHOULD: Give a proper intro about sed -->
<p>If you need to operate on more than individual characters, then you may find <code>sed</code> useful.
You’ve already seen an example of <code>sed</code> with extracting the chapter headings from <em>alice.txt</em>.
Extracting, deleting, and replacing is actually all the same operation in <code>sed</code>.
You just specify different regular expressions.
For example, to change a word, remove repeated spaces, and remove leading spaces:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">echo</span> <span style="color: #af8700">' hello     world!'</span> |
<span style="font-weight: bold">&gt;</span> <span style="color: #5f8700">sed</span> -re <span style="color: #af8700">'s/hello/bye/'</span> | <span class="callout">➊</span>
<span style="font-weight: bold">&gt;</span> <span style="color: #5f8700">sed</span> -re <span style="color: #af8700">'s/\s+/ /g'</span> | <span class="callout">➋</span>
<span style="font-weight: bold">&gt;</span> <span style="color: #5f8700">sed</span> -re <span style="color: #af8700">'s/\s+//'</span> <span class="callout">➌</span>
bye world!</pre>
<p><span class="callout">➊</span> Replace <em>hello</em> with <em>bye</em>.
<br><span class="callout">➋</span> Replace any whitespace with one space. The flag <code>g</code> stands for global, meaning that the same substitution can be applied more than once on the same line.
<br><span class="callout">➌</span> This only removes leading spaces because I didn’t specify the flag <code>g</code> here.</p>
<p>Again, just as with the <code>grep</code> example earlier, these three <code>sed</code> commands can be combined into one:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">echo</span> <span style="color: #af8700">' hello     world!'</span> |
<span style="font-weight: bold">&gt;</span> <span style="color: #5f8700">sed</span> -re <span style="color: #af8700">'s/hello/bye/;s/\s+/ /g;s/\s+//'</span>
bye world!</pre>
<p>But tell me, what do you find easier to read?</p>
</div>
</div>
<div id="csv" class="section level2" number="5.4">
<h2>
<span class="header-section-number">5.4</span> CSV<a class="anchor" aria-label="anchor" href="#csv"><i class="fas fa-link"></i></a>
</h2>
<!-- #TODO: MUST: ### Canonical Format -->
<!-- The `csvkit` documentation[@csvkit] opens with: [...] CSV, the king of tabular file formats. -->
<!-- Canonical format. The format that we want to convert to. -->
<!-- Two properties. it's plain text and it has a rectangular shape, meaning in consists of rows and columns. -->
<!-- As a format, -->
<!-- As a shape, -->
<!-- Even kings have their flaws. -->
<!-- CSV definitely has its flaws. No meta data. No rules regarding quoting, the delimiter, header. -->
<!-- Writing a robust CSV parser is really hard. Nevertheless, CSV has many advantages. -->
<!-- CSV, which is the main format I'll be working with in this chapter, is actually not the easiest format to work with. Many CSV datasets are broken or incompatible with each other because there is no standard syntax, unlike XML and JSON. -->
<!-- Export from Database, spreadsheets, rectangular. -->
<!-- - It's still plain text, so can be read, edited, and even created in any text editor. -->
<!-- - CSV can be imported by many programming languages such as Python, R, and JavaScript and many software such as Excel, Tableau, and Power BI. -->
<!-- - Related to the previous point, but worth mentioning separately: a CSV directly translates to a data frame in Python (with the pandas package), R, and Julia. This means that can immediately continue scrubbing, exploring, and modeling in those languages. -->
<!-- - Speaking of modeling, most machine learning algorithms expect data to be in a rectangular format, or more precisely, a matrix of numerical values where each row is a data point and each column is a feature. More on this in Chapter 9. -->
<!-- makes assumptions, same number of fields per row. -->
<!-- flat. not nested. -->
<!-- CVS is not nested structure -->
<!-- If you build an API, use JSON -->
<!-- If you -->
<!-- You rarely have to covert CSV to anything else when you're doing data analysis. -->
<div id="bodies-and-headers-and-columns-oh-my" class="section level3" number="5.4.1">
<h3>
<span class="header-section-number">5.4.1</span> Bodies and Headers and Columns, Oh My!<a class="anchor" aria-label="anchor" href="#bodies-and-headers-and-columns-oh-my"><i class="fas fa-link"></i></a>
</h3>
<p>The command-line tools that I’ve used to scrub plain text, such as <code>tr</code> and <code>grep</code>, cannot always be applied to CSV.
The reason is that these command-line tools have no notion of headers, bodies, and columns.
What if you want to filter lines using <code>grep</code> but always include the header in the output?
Or what if you only want to uppercase the values of a specific column using <code>tr</code> and leave the other columns untouched?</p>
<p>There are multi-step workarounds for this, but they are very cumbersome.
I have something better.
In order to leverage ordinary command-line tools for CSV, I’d like to introduce you to three command-line tools, aptly named: <code>body</code><span class="citation"><a class="footnote-ref" tabindex="0" data-toggle="popover" data-content='&lt;p&gt;&lt;a href="#ref-body" role="doc-biblioref"&gt;Jeroen Janssens, &lt;em&gt;&lt;span class="nocase"&gt;body&lt;/span&gt; – Apply Command to All but the First Line&lt;/em&gt;, version 0.1, 2021, &lt;/a&gt;&lt;a href="https://github.com/jeroenjanssens/dsutils" role="doc-biblioref"&gt;https://github.com/jeroenjanssens/dsutils&lt;/a&gt;.&lt;/p&gt;'><sup>75</sup></a></span>, <code>header</code><span class="citation"><a class="footnote-ref" tabindex="0" data-toggle="popover" data-content='&lt;p&gt;&lt;a href="#ref-header" role="doc-biblioref"&gt;Jeroen Janssens, &lt;em&gt;&lt;span class="nocase"&gt;header&lt;/span&gt; – Add, Replace, and Delete Header Lines&lt;/em&gt;, version 0.1, 2021, &lt;/a&gt;&lt;a href="https://github.com/jeroenjanssens/dsutils" role="doc-biblioref"&gt;https://github.com/jeroenjanssens/dsutils&lt;/a&gt;.&lt;/p&gt;'><sup>76</sup></a></span>, and <code>cols</code><span class="citation"><a class="footnote-ref" tabindex="0" data-toggle="popover" data-content='&lt;p&gt;&lt;a href="#ref-cols" role="doc-biblioref"&gt;Jeroen Janssens, &lt;em&gt;&lt;span class="nocase"&gt;cols&lt;/span&gt; – Apply Command to Subset of Columns&lt;/em&gt;, version 0.1, 2021, &lt;/a&gt;&lt;a href="https://github.com/jeroenjanssens/dsutils" role="doc-biblioref"&gt;https://github.com/jeroenjanssens/dsutils&lt;/a&gt;.&lt;/p&gt;'><sup>77</sup></a></span>.</p>
<p>Let’s start with the first command-line tool, <code>body</code>.
With <code>body</code> you can apply any command-line tool to the body of a CSV file, that is, everything excluding the header.
For example:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">echo</span> -e <span style="color: #af8700">"value\n7\n2\n5\n3"</span> | <span style="color: #5f8700">body</span> sort -n
value
2
3
5
7</pre>
<p>It assumes that the header of the CSV file only spans one row.
It works like this:</p>
<ul>
<li>Take one line from standard in and store it as a variable named <em>$header</em>.</li>
<li>Print out the header.</li>
<li>Execute all the command-line arguments passed to <code>body</code> on the remaining data in standard in.</li>
</ul>
<p>Here’s another example.
Imagine that you want to count the lines of the following CSV file:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">seq</span> 5 | <span style="color: #5f8700">header</span> -a count
count
1
2
3
4
5</pre>
<p>With <code>wc -l</code>, you can count the number of all lines:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">seq</span> 5 | <span style="color: #5f8700">header</span> -a count | <span style="color: #5f8700">wc</span> -l
6</pre>
<p>If you only want to consider the lines in the body (so everything except the header), you add <code>body</code>:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">seq</span> 5 | <span style="color: #5f8700">header</span> -a count | <span style="color: #5f8700">body</span> wc -l
count
5</pre>
<p>Note that the header is not used and is also printed again in the output.</p>
<p>The second command-line tool, <code>header</code> allows you to manipulate the header of a CSV file.
If no arguments are provided, the header of the CSV file is printed:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">tips.csv</span> <span style="color: #5f8700">header</span>
bill,tip,sex,smoker,day,time,size</pre>
<p>This is the same as <code>head -n 1</code>.
If the header spans more than one row, which is not recommended, you can specify <code>-n 2</code>.
You can also add a header to a CSV file:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">seq</span> 5 | <span style="color: #5f8700">header</span> -a count
count
1
2
3
4
5</pre>
<p>This is equivalent to <code>echo "count" | cat - &lt;(seq 5)</code>.
Deleting a header is done with the <code>-d</code> option:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">iris.csv</span> <span style="color: #5f8700">header</span> -d | <span style="color: #5f8700">trim</span>
5.1,3.5,1.4,0.2,Iris-setosa
4.9,3.0,1.4,0.2,Iris-setosa
4.7,3.2,1.3,0.2,Iris-setosa
4.6,3.1,1.5,0.2,Iris-setosa
5.0,3.6,1.4,0.2,Iris-setosa
5.4,3.9,1.7,0.4,Iris-setosa
4.6,3.4,1.4,0.3,Iris-setosa
5.0,3.4,1.5,0.2,Iris-setosa
4.4,2.9,1.4,0.2,Iris-setosa
4.9,3.1,1.5,0.1,Iris-setosa
… with 140 more lines</pre>
<p>This is similar to <code>tail -n +2</code>, but it’s a bit easier to remember.
Replacing a header, which is basically first deleting a header and then adding one if you look at the above source code, is accomplished by specifying the <code>-r</code> option. Here, we combine it with <code>body</code>:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">seq</span> 5 | <span style="color: #5f8700">header</span> -a line | <span style="color: #5f8700">body</span> wc -l | <span style="color: #5f8700">header</span> -r count
count
5</pre>
<p>And last but not least, you can apply a command to just the header, similar to what the <code>body</code> command-line tool does to the body.
For example:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">seq</span> 5 | <span style="color: #5f8700">header</span> -a line | <span style="color: #5f8700">header</span> -e <span style="color: #af8700">"tr '[a-z]' '[A-Z]'"</span>
LINE
1
2
3
4
5</pre>
<p>The third command-line tool is called <code>cols</code>, which allows you to apply a certain command to only a subset of the columns.
For example, if you wanted to uppercase the values in the day column in the tips data set (without affecting the other columns and the header), you would use <code>cols</code> in combination with <code>body</code>, as follows:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">tips.csv</span> <span style="color: #5f8700">cols</span> -c day body <span style="color: #af8700">"tr '[a-z]' '[A-Z]'"</span> | <span style="color: #5f8700">head</span> -n 5 | <span style="color: #5f8700">csvlook</span>
│        day │  bill │  tip │ sex    │ smoker │ time   │ size │
├────────────┼───────┼──────┼────────┼────────┼────────┼──────┤
│ 0001-01-07 │ 16.99 │ 1.01 │ Female │  False │ Dinner │    2 │
│ 0001-01-07 │ 10.34 │ 1.66 │ Male   │  False │ Dinner │    3 │
│ 0001-01-07 │ 21.01 │ 3.50 │ Male   │  False │ Dinner │    3 │
│ 0001-01-07 │ 23.68 │ 3.31 │ Male   │  False │ Dinner │    2 │</pre>
<p>Please note that passing multiple command-line tools and arguments as command to <code>header -e</code>, <code>body</code>, and <code>cols</code> can lead to tricky quoting citations.
If you ever run in such problems, it’s best to create a separate command-line tool for this and pass that as command.</p>
<p>In conclusion, while it is generally preferable to use command-line tools which are specifically made for CSV data, <code>body</code>, <code>header</code>, and <code>cols</code> also allow you to apply the classic command-line tools to CSV files if needed.</p>
</div>
<div id="performing-sql-queries-on-csv" class="section level3" number="5.4.2">
<h3>
<span class="header-section-number">5.4.2</span> Performing SQL Queries on CSV<a class="anchor" aria-label="anchor" href="#performing-sql-queries-on-csv"><i class="fas fa-link"></i></a>
</h3>
<p>In case the command-line tools mentioned in this chapter do not provide enough flexibility, then there is another approach to scrub your data from the command line.
The tool <code>csvsql</code><span class="citation"><a class="footnote-ref" tabindex="0" data-toggle="popover" data-content='&lt;p&gt;&lt;a href="#ref-csvsql" role="doc-biblioref"&gt;Christopher Groskopf, &lt;em&gt;&lt;span class="nocase"&gt;csvsql&lt;/span&gt; – Execute &lt;span&gt;SQL&lt;/span&gt; Statements on &lt;span&gt;CSV&lt;/span&gt; Files&lt;/em&gt;, version 1.0.5, 2020, &lt;/a&gt;&lt;a href="https://csvkit.rtfd.org" role="doc-biblioref"&gt;https://csvkit.rtfd.org&lt;/a&gt;.&lt;/p&gt;'><sup>78</sup></a></span> allows you to execute SQL queries directly on CSV files.
SQL is a powerful language to define operations for scrubbing data; it’s a very different way than using individual command-line tools.</p>

<div class="rmdnote">
If your data originally comes from a relational database, then, if possible, try to execute SQL queries on that database and subsequently extract the data as CSV. As I discussed in <a href="chapter-3-obtaining-data.html#chapter-3-obtaining-data">Chapter 3</a>, you can use the command-line tool <code>sql2csv</code> for this. When you first export data from the database to a CSV file, and then apply SQL, it is not only slower, but there is also a possibility that the column types are not correctly inferred from the CSV data.
</div>
<p>In the scrubbing tasks below, I’ll include several solutions that involve <code>csvsql</code>. A basic command is this:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">seq</span> 5 | <span style="color: #5f8700">header</span> -a val | <span style="color: #5f8700">csvsql</span> --query <span style="color: #af8700">"SELECT SUM(val) AS sum FROM stdin"</span>
sum
15.0</pre>
<p>If you pass standard input to <code>csvsql</code>, then the table is named <em>stdin</em>.
The types of the column are automatically inferred from the data.
As you’ll see later, in the combining CSV files section, you can also specify multiple CSV files.
<!-- #TODO: MUST: Reference SQLite -->
Please keep in mind that <code>csvsql</code> employs SQLite dialect of SQL, which has some subtle differences with respect to the SQL standard.
While SQL is generally more verbose than the other solutions, it is also much more flexible.
If you already know how to tackle a scrubbing problem with SQL, then why not use it when you’re at the command line?</p>
</div>
<div id="extracting-and-reordering-columns" class="section level3" number="5.4.3">
<h3>
<span class="header-section-number">5.4.3</span> Extracting and Reordering Columns<a class="anchor" aria-label="anchor" href="#extracting-and-reordering-columns"><i class="fas fa-link"></i></a>
</h3>
<!-- #TODO: MUST: Replace csvcut with xsv select -->
<p>Columns can be extracted and reordered using the command-line tool: <code>csvcut</code><span class="citation"><a class="footnote-ref" tabindex="0" data-toggle="popover" data-content='&lt;p&gt;&lt;a href="#ref-csvcut" role="doc-biblioref"&gt;Christopher Groskopf, &lt;em&gt;&lt;span class="nocase"&gt;csvcut&lt;/span&gt; – Filter and Truncate &lt;span&gt;CSV&lt;/span&gt; Files&lt;/em&gt;, version 1.0.5, 2020, &lt;/a&gt;&lt;a href="https://csvkit.rtfd.org" role="doc-biblioref"&gt;https://csvkit.rtfd.org&lt;/a&gt;.&lt;/p&gt;'><sup>79</sup></a></span>.
For example, to keep only the columns in the Iris data set that contain numerical values <em>and</em> reorder the middle two columns:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">iris.csv</span> <span style="color: #5f8700">csvcut</span> -c sepal_length,petal_length,sepal_width,petal_width | <span style="color: #5f8700">csvlo
ok</span>
│ sepal_length │ petal_length │ sepal_width │ petal_width │
├──────────────┼──────────────┼─────────────┼─────────────┤
│          5.1 │          1.4 │         3.5 │         0.2 │
│          4.9 │          1.4 │         3.0 │         0.2 │
│          4.7 │          1.3 │         3.2 │         0.2 │
│          4.6 │          1.5 │         3.1 │         0.2 │
│          5.0 │          1.4 │         3.6 │         0.2 │
│          5.4 │          1.7 │         3.9 │         0.4 │
│          4.6 │          1.4 │         3.4 │         0.3 │
│          5.0 │          1.5 │         3.4 │         0.2 │
… with 142 more lines</pre>
<p>Alternatively, you can also specify the columns you want to leave out with the <code>-C</code> option, which stands for <em>complement</em>:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">iris.csv</span> <span style="color: #5f8700">csvcut</span> -C species | <span style="color: #5f8700">csvlook
</span>│ sepal_length │ sepal_width │ petal_length │ petal_width │
├──────────────┼─────────────┼──────────────┼─────────────┤
│          5.1 │         3.5 │          1.4 │         0.2 │
│          4.9 │         3.0 │          1.4 │         0.2 │
│          4.7 │         3.2 │          1.3 │         0.2 │
│          4.6 │         3.1 │          1.5 │         0.2 │
│          5.0 │         3.6 │          1.4 │         0.2 │
│          5.4 │         3.9 │          1.7 │         0.4 │
│          4.6 │         3.4 │          1.4 │         0.3 │
│          5.0 │         3.4 │          1.5 │         0.2 │
… with 142 more lines</pre>
<p>Here, the included columns are kept in the same order.
Instead of the column names, you can also specify the indices of the columns, which start at 1.
This allows you to, for example, select only the odd columns (should you ever need it!):</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">echo</span> <span style="color: #af8700">'a,b,c,d,e,f,g,h,i\n1,2,3,4,5,6,7,8,9'</span> |
<span style="font-weight: bold">&gt;</span> <span style="color: #5f8700">csvcut</span> -c <span style="color: #af005f">$(</span><span style="color: #5f8700">seq</span> 1 2 9 | <span style="color: #5f8700">paste</span> -sd,<span style="color: #af005f">)</span>
a,c,e,g,i
1,3,5,7,9</pre>
<p>If you’re certain that there are no comma’s in any of the values, then you can also use <code>cut</code> to extract columns.
Be aware that <code>cut</code> does not reorder columns, as is demonstrated with the following command:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">echo</span> <span style="color: #af8700">'a,b,c,d,e,f,g,h,i\n1,2,3,4,5,6,7,8,9'</span> | <span style="color: #5f8700">cut</span> -d, -f 5,1,3
a,c,e
1,3,5</pre>
<p>As you can see, it does not matter in which order you specify the columns with the <code>-f</code> option; with <code>cut</code> they will always appear in the original order.
For completeness, let’s also take a look at the SQL approach for extracting and reordering the numerical columns of the Iris data set:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">iris.csv</span> <span style="color: #5f8700">csvsql</span> --query <span style="color: #af8700">"SELECT sepal_length, petal_length, "</span>\
<span style="font-weight: bold">&gt;</span> <span style="color: #af8700">"sepal_width, petal_width FROM stdi</span>n<span style="color: #5f8700">" | </span>head <span style="color: #5f8700">-n 5 | </span>csvlook
│ sepal_length │ petal_length │ sepal_width │ petal_width │
├──────────────┼──────────────┼─────────────┼─────────────┤
│          5.1 │          1.4 │         3.5 │         0.2 │
│          4.9 │          1.4 │         3.0 │         0.2 │
│          4.7 │          1.3 │         3.2 │         0.2 │
│          4.6 │          1.5 │         3.1 │         0.2 │</pre>
</div>
<div id="filtering-rows" class="section level3" number="5.4.4">
<h3>
<span class="header-section-number">5.4.4</span> Filtering Rows<a class="anchor" aria-label="anchor" href="#filtering-rows"><i class="fas fa-link"></i></a>
</h3>
<p>The difference between filtering rows in a CSV file as opposed to filtering lines in a plain text file is that you may want to base this filtering on values in a certain column, only.
Filtering on location is essentially the same, but you have to take into account that the first line of a CSV file is usually the header.
Remember that you can always use the <code>body</code> command-line tool if you want to keep the header:</p>
<!-- #TODO: #MUST use xsv slice and xsv search -->
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">seq</span> 5 | <span style="color: #5f8700">sed</span> -n <span style="color: #af8700">'3,5p'</span>
3
4
5
 
<span style="font-weight: bold">$</span> <span style="color: #5f8700">seq</span> 5 | <span style="color: #5f8700">header</span> -a count | <span style="color: #5f8700">body</span> sed -n <span style="color: #af8700">'3,5p'</span>
count
3
4
5</pre>
<p>When it comes down to filtering on a certain pattern within a certain column, you can use either <code>csvgrep</code><span class="citation"><a class="footnote-ref" tabindex="0" data-toggle="popover" data-content='&lt;p&gt;&lt;a href="#ref-csvgrep" role="doc-biblioref"&gt;Christopher Groskopf, &lt;em&gt;&lt;span class="nocase"&gt;csvgrep&lt;/span&gt; – Search &lt;span&gt;CSV&lt;/span&gt; Files&lt;/em&gt;, version 1.0.5, 2020, &lt;/a&gt;&lt;a href="https://csvkit.rtfd.org" role="doc-biblioref"&gt;https://csvkit.rtfd.org&lt;/a&gt;.&lt;/p&gt;'><sup>80</sup></a></span>, <code>awk</code>, or, of course, <code>csvsql</code>.
For example, to exclude all the bills of which the party size was smaller than 5:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">csvgrep</span> -c size -i -r <span style="color: #af8700">"[1-4]"</span> <span style="text-decoration: underline">tips.csv
</span>bill,tip,sex,smoker,day,time,size
29.8,4.2,Female,No,Thur,Lunch,6
34.3,6.7,Male,No,Thur,Lunch,6
41.19,5.0,Male,No,Thur,Lunch,5
27.05,5.0,Female,No,Thur,Lunch,6
29.85,5.14,Female,No,Sun,Dinner,5
48.17,5.0,Male,No,Sun,Dinner,6
20.69,5.0,Male,No,Sun,Dinner,5
30.46,2.0,Male,Yes,Sun,Dinner,5
28.15,3.0,Male,Yes,Sat,Dinner,5</pre>
<p>Both <code>awk</code> and <code>csvsql</code> can also do numerical comparisons.
For example, to get all the bills above 40 USD on a Saturday or a Sunday:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">tips.csv</span> <span style="color: #5f8700">awk</span> -F, <span style="color: #af8700">'NR==1 || ($1 &gt; 40.0) &amp;&amp; ($5 ~ /^S/)'</span>
bill,tip,sex,smoker,day,time,size
48.27,6.73,Male,No,Sat,Dinner,4
44.3,2.5,Female,Yes,Sat,Dinner,3
48.17,5.0,Male,No,Sun,Dinner,6
50.81,10.0,Male,Yes,Sat,Dinner,3
45.35,3.5,Male,Yes,Sun,Dinner,3
40.55,3.0,Male,Yes,Sun,Dinner,2
48.33,9.0,Male,No,Sat,Dinner,4</pre>
<p>The <code>csvsql</code> solution is more verbose but it’s also more robust as it uses the names of the columns instead of their indexes:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">csvsql</span> --query <span style="color: #af8700">"SELECT * FROM tips WHERE bill &gt; 40 AND day LIKE 'S%'"</span> <span style="text-decoration: underline">tips.csv
</span>
bill,tip,sex,smoker,day,time,size
48.27,6.73,Male,0,Sat,Dinner,4.0
44.3,2.5,Female,1,Sat,Dinner,3.0
48.17,5.0,Male,0,Sun,Dinner,6.0
50.81,10.0,Male,1,Sat,Dinner,3.0
45.35,3.5,Male,1,Sun,Dinner,3.0
40.55,3.0,Male,1,Sun,Dinner,2.0
48.33,9.0,Male,0,Sat,Dinner,4.0</pre>
<p>Note that the flexibility of the <em>WHERE</em> clause in an SQL query cannot be easily matched with other command-line tools, because SQL can operate on dates and sets, and form complex combinations of clauses.</p>
</div>
<div id="merging-columns" class="section level3" number="5.4.5">
<h3>
<span class="header-section-number">5.4.5</span> Merging Columns<a class="anchor" aria-label="anchor" href="#merging-columns"><i class="fas fa-link"></i></a>
</h3>
<p>Merging columns is useful for when the values of interest are spread over multiple columns.
This may happen with dates (where year, month, and day could be separate columns) or names (where the first name and last name are separate columns).
Let’s consider the second situation.</p>
<!-- #TODO: Must use composers.csv -->
<p>The input CSV is a list of composers.
Imagine that your task is to combine the first name and the last name into a full name.
I’ll present four different approaches for this task: <code>sed</code>, <code>awk</code>, <code>cols</code> + <code>tr</code>, and <code>csvsql</code>.
Let’s have a look at the input CSV:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">csvlook</span> -I <span style="text-decoration: underline">names.csv</span>
│ id │ last_name │ first_name │ born │
├────┼───────────┼────────────┼──────┤
│ 1  │ Williams  │ John       │ 1932 │
│ 2  │ Elfman    │ Danny      │ 1953 │
│ 3  │ Horner    │ James      │ 1953 │
│ 4  │ Shore     │ Howard     │ 1946 │
│ 5  │ Zimmer    │ Hans       │ 1957 │</pre>
<p>The first approach, <code>sed</code>, uses two statements.
The first is to replace the header and the second is a regular expression with back references applied to the second row onwards:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">names.csv</span> <span style="color: #5f8700">sed</span> -re <span style="color: #af8700">'1s/.*/id,full_name,born/g;2,$s/(.*),(.*),(.*),(.*)/\1,\3
\2,\4/g'</span> |
<span style="font-weight: bold">&gt;</span> <span style="color: #5f8700">csvlook</span> -I
│ id │ full_name     │ born │
├────┼───────────────┼──────┤
│ 1  │ John Williams │ 1932 │
│ 2  │ Danny Elfman  │ 1953 │
│ 3  │ James Horner  │ 1953 │
│ 4  │ Howard Shore  │ 1946 │
│ 5  │ Hans Zimmer   │ 1957 │</pre>
<p>The <code>awk</code> approach looks as follows:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">names.csv</span> <span style="color: #5f8700">awk</span> -F, <span style="color: #af8700">'BEGIN{OFS=","; print "id,full_name,born"} {if(NR &gt; 1) {pr
int $1,$3" "$2,$4}}'</span> |
<span style="font-weight: bold">&gt;</span> <span style="color: #5f8700">csvlook</span> -I
│ id │ full_name     │ born │
├────┼───────────────┼──────┤
│ 1  │ John Williams │ 1932 │
│ 2  │ Danny Elfman  │ 1953 │
│ 3  │ James Horner  │ 1953 │
│ 4  │ Howard Shore  │ 1946 │
│ 5  │ Hans Zimmer   │ 1957 │</pre>
<p>The <code>cols</code> approach in combination with <code>tr</code>:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">names.csv</span> <span style="font-weight: bold"></span><span style="font-weight: bold; color: #d70000">|</span>
<span style="font-weight: bold">&gt;</span> <span style="color: #5f8700">cols</span> -c first_name,last_name tr \",\" \" \" |
<span style="font-weight: bold">&gt;</span> <span style="color: #5f8700">header</span> -r full_name,id,born |
<span style="font-weight: bold">&gt;</span> <span style="color: #5f8700">csvcut</span> -c id,full_name,born |
<span style="font-weight: bold">&gt;</span> <span style="color: #5f8700">csvlook</span> -I
│ id │ full_name     │ born │
├────┼───────────────┼──────┤
│ 1  │ John Williams │ 1932 │
│ 2  │ Danny Elfman  │ 1953 │
│ 3  │ James Horner  │ 1953 │
│ 4  │ Howard Shore  │ 1946 │
│ 5  │ Hans Zimmer   │ 1957 │</pre>
<p>Please note that <code>csvsql</code> employ SQLite as the database to execute the query and that <code>||</code> stands for concatenation:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">names.csv</span> <span style="color: #5f8700">csvsql</span> --query <span style="color: #af8700">"SELECT id, first_name || ' ' || last_name "</span>\
<span style="font-weight: bold">&gt;</span> <span style="color: #af8700">"AS full_name, born FROM stdi</span>n<span style="color: #5f8700">" | csv</span>look -I
│ id  │ full_name     │ born   │
├─────┼───────────────┼────────┤
│ 1.0 │ John Williams │ 1932.0 │
│ 2.0 │ Danny Elfman  │ 1953.0 │
│ 3.0 │ James Horner  │ 1953.0 │
│ 4.0 │ Howard Shore  │ 1946.0 │
│ 5.0 │ Hans Zimmer   │ 1957.0 │</pre>
<p>What if <em>last_name</em> would contain a comma? Let’s have a look at the raw input CSV for clarity sake:</p>
<!-- Ludwig van Beethoven enters the party -->
<!-- ```{console} -->
<!-- echo 'Ludwig,"Beethoven, van",1770,"Bonn,\nGermany"' >> composers.csv -->
<!-- ``` -->
<!-- bat -A composers.csv -->
<!-- //# cat composers.csv | csvquote | tr -d $'\x1f'   # comma (unit separator in unicode) -->
<!-- //# cat composers.csv | csvquote | tr $'\x1e' ' '  # new line (record separator in unicode) -->
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">cat</span> <span style="text-decoration: underline">names-comma.csv</span>
id,last_name,first_name,born
1,Williams,John,1932
2,Elfman,Danny,1953
3,Horner,James,1953
4,Shore,Howard,1946
5,Zimmer,Hans,1957
6,"Beethoven, van",Ludwig,1770</pre>
<p>Well, it appears that the first three approaches fail; all in different ways. Only <code>csvsql</code> is able to combine first_name and full_name:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">names-comma.csv</span> <span style="color: #5f8700">sed</span> -re <span style="color: #af8700">'1s/.*/id,full_name,born/g;2,$s/(.*),(.*),(.*),(.*)/
\1,\3 \2,\4/g'</span> | <span style="color: #5f8700">tail</span> -n 1
6,"Beethoven,Ludwig  van",1770</pre>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">names-comma.csv</span> <span style="color: #5f8700">awk</span> -F, <span style="color: #af8700">'BEGIN{OFS=","; print "id,full_name,born"} {if(NR &gt;
1) {print $1,$3" "$2,$4}}'</span> | <span style="color: #5f8700">tail</span> -n 1
6, van" "Beethoven,Ludwig</pre>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">names-comma.csv</span> <span style="font-weight: bold"></span><span style="font-weight: bold; color: #d70000">|</span> <span style="color: #5f8700">cols</span> -c first_name,last_name tr \",\" \" \" |
<span style="font-weight: bold">&gt;</span> <span style="color: #5f8700">header</span> -r full_name,id,born | <span style="color: #5f8700">csvcut</span> -c id,full_name,born | <span style="color: #5f8700">tail</span> -n 1
6,"Ludwig ""Beethoven  van""",1770</pre>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">names-comma.csv</span> <span style="color: #5f8700">csvsql</span> --query <span style="color: #af8700">"SELECT id, first_name || ' ' || last_name AS
 full_name, born FROM stdin"</span> | <span style="color: #5f8700">tail</span> -n 1
6.0,"Ludwig Beethoven, van",1770.0</pre>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">names-comma.csv</span> <span style="color: #5f8700">rush</span> run -t <span style="color: #af8700">'unite(df, full_name, first_name, last_name, sep
 = " ")'</span> - | <span style="color: #5f8700">tail</span> -n 1
6,"Ludwig Beethoven, van",1770</pre>
<p>Wait a minute! What’s that last command? Is that R? Well, as a matter of fact, it is.
It’s R code evaluated through a command-line tool called <code>rush</code>. All that I can say at this moment, is that also this approach succeeds at merging the two columns.
I’ll discuss this nifty command-line tool later.</p>
</div>
<div id="combining-multiple-csv-files" class="section level3" number="5.4.6">
<h3>
<span class="header-section-number">5.4.6</span> Combining Multiple CSV Files<a class="anchor" aria-label="anchor" href="#combining-multiple-csv-files"><i class="fas fa-link"></i></a>
</h3>
<!-- #### Concatenate Vertically -->
<!-- Vertical concatenation may be necessary in cases where you have, for example, a data set which is generated on a daily basis, or where each data set represents a different, say, market or product. -->
<!-- Let’s simulate the former by splitting up *scientists.csv*, so that we have something to combine again. -->
<!-- <\!-- #TODO: use xsv split -\-> -->
<!-- <\!-- #TODO mention that this could also be useful for parallel processing (Chapter 8) -\-> -->
<!-- <\!-- #TODO: use xsv cat instead of csvstack -\-> -->
<!-- ```{console} -->
<!--  < scientists.xml xml2json | jq -r '.Root.data.record[] | [.field[0]."$t", .field[2]."$t", .field[3]."$t"] | @csv' | header -a "country,year,value" > scientists.csv -->
<!-- xsv split -s 2000 science scientists.csv -->
<!-- wc -l science/* -->
<!-- csvlook science/12000.csv -->
<!-- ``` -->
<!-- You could just concatenate the files using `cat` and removing the headers of all but the first file using as follows: -->
<!-- A more robust solution to combine multiple CSV files is to use `xsv`: -->
<!-- Out of order -->
<!-- ```{console} -->
<!-- xsv cat rows science/*.csv | tail -->
<!-- ``` -->
<!-- Note that the rows have -->
<!-- Back in order -->
<!-- ```{console} -->
<!-- echo $(ls -v science/*.csv) -->
<!-- xsv cat rows $(ls -v science/*.csv) | tail -->
<!-- ``` -->
<!-- ```{console} -->
<!-- awk 'NR==1 {H=$0; print} $0!=H' 2021-01-0*.csv | trim -->
<!-- ``` -->
<!-- ```{console} -->
<!-- shuf -ri 0-9999 -n 24 | nl -v0 -w1 -s, | header -a hour,value > 2021-01-01.csv -->
<!-- shuf -ri 0-9999 -n 24 | nl -v0 -w1 -s, | header -a hour,value > 2021-01-02.csv -->
<!-- shuf -ri 0-9999 -n 24 | nl -v0 -w1 -s, | header -a hour,value > 2021-01-03.csv -->
<!-- head -n 3 2021-*.csv -->
<!-- ``` -->
<!-- ```{console} -->
<!-- csvstack --group-name date --filenames 2021-*.csv | sed 's/\.csv,/,/' | csvlook -->
<!-- ``` -->
<!-- ```{console} -->
<!-- awk 'NR==1 {H=$0; print "date,"$0} $0!=H {print FILENAME","$0}' 2021-01-0*.csv | csvlook -->
<!-- ``` -->
<div id="concatenate-horizontally" class="section level4" number="5.4.6.1">
<h4>
<span class="header-section-number">5.4.6.1</span> Concatenate Horizontally<a class="anchor" aria-label="anchor" href="#concatenate-horizontally"><i class="fas fa-link"></i></a>
</h4>
<p>Let’s say you have three CSV files that want to put side by side. We use <code>tee</code><span class="citation"><a class="footnote-ref" tabindex="0" data-toggle="popover" data-content='&lt;p&gt;&lt;a href="#ref-tee" role="doc-biblioref"&gt;Mike Parker, Richard M. Stallman, and David MacKenzie, &lt;em&gt;&lt;span class="nocase"&gt;tee&lt;/span&gt; – Read from Standard Input and Write to Standard Output and Files&lt;/em&gt;, version 8.30, 2019, &lt;/a&gt;&lt;a href="https://www.gnu.org/software/coreutils" role="doc-biblioref"&gt;https://www.gnu.org/software/coreutils&lt;/a&gt;.&lt;/p&gt;'><sup>81</sup></a></span> to save the result of <code>csvcut</code> in the middle of the pipeline:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">tips.csv</span> <span style="color: #5f8700">csvcut</span> -c bill,tip | <span style="color: #5f8700">tee</span> bills.csv | <span style="color: #5f8700">head</span> -n 3 | <span style="color: #5f8700">csvlook</span>
│  bill │  tip │
├───────┼──────┤
│ 16.99 │ 1.01 │
│ 10.34 │ 1.66 │
 
<span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">tips.csv</span> <span style="color: #5f8700">csvcut</span> -c day,time | <span style="color: #5f8700">tee</span> datetime.csv |
<span style="font-weight: bold">&gt;</span> <span style="color: #5f8700">head</span> -n 3 | <span style="color: #5f8700">csvlook</span> -I
│ day │ time   │
├─────┼────────┤
│ Sun │ Dinner │
│ Sun │ Dinner │
 
<span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">tips.csv</span> <span style="color: #5f8700">csvcut</span> -c sex,smoker,size | <span style="color: #5f8700">tee</span> customers.csv |
<span style="font-weight: bold">&gt;</span> <span style="color: #5f8700">head</span> -n 3 | <span style="color: #5f8700">csvlook</span>
│ sex    │ smoker │ size │
├────────┼────────┼──────┤
│ Female │  False │    2 │
│ Male   │  False │    3 │</pre>
<p>Assuming that the rows line up, you can <code>paste</code><span class="citation"><a class="footnote-ref" tabindex="0" data-toggle="popover" data-content='&lt;p&gt;&lt;a href="#ref-paste" role="doc-biblioref"&gt;Ihnat and MacKenzie, &lt;em&gt;&lt;span class="nocase"&gt;paste&lt;/span&gt; – Merge Lines of Files&lt;/em&gt;&lt;/a&gt;.&lt;/p&gt;'><sup>82</sup></a></span> the files together:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">paste</span> -d, {bills,customers,datetime}.csv | <span style="color: #5f8700">head</span> -n 3 | <span style="color: #5f8700">csvlook</span> -I
│ bill  │ tip  │ sex    │ smoker │ size │ day │ time   │
├───────┼──────┼────────┼────────┼──────┼─────┼────────┤
│ 16.99 │ 1.01 │ Female │ No     │ 2    │ Sun │ Dinner │
│ 10.34 │ 1.66 │ Male   │ No     │ 3    │ Sun │ Dinner │</pre>
<p>Here, the command-line argument <code>-d</code> instructs <code>paste</code> to use a comma as the delimiter.</p>
</div>
<div id="joining" class="section level4" number="5.4.6.2">
<h4>
<span class="header-section-number">5.4.6.2</span> Joining<a class="anchor" aria-label="anchor" href="#joining"><i class="fas fa-link"></i></a>
</h4>
<p>Sometimes data cannot be combined by vertical or horizontal concatenation.
In some cases, especially in relational databases, the data is spread over multiple tables (or files) in order to minimize redundancy.
Imagine you wanted to extend the Iris data set with more information about the three types of Iris flowers, namely the USDA identifier.
It so happens that I have a separate CSV file with these identifiers:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">csvlook</span> <span style="text-decoration: underline">irismeta.csv</span>
│ species         │ wikipedia_url                                │ usda_id │
├─────────────────┼──────────────────────────────────────────────┼─────────┤
│ Iris-versicolor │ http://en.wikipedia.org/wiki/Iris_versicolor │ IRVE2   │
│ Iris-virginica  │ http://en.wikipedia.org/wiki/Iris_virginica  │ IRVI    │
│ Iris-setosa     │                                              │ IRSE    │</pre>
<p>What this data set and the Iris data set have in common is the <em>species</em> column.
You can use <code>csvjoin</code><span class="citation"><a class="footnote-ref" tabindex="0" data-toggle="popover" data-content='&lt;p&gt;&lt;a href="#ref-csvjoin" role="doc-biblioref"&gt;Christopher Groskopf, &lt;em&gt;&lt;span class="nocase"&gt;csvjoin&lt;/span&gt; – Execute a &lt;span&gt;SQL&lt;/span&gt;-Like Join to Merge &lt;span&gt;CSV&lt;/span&gt; Files on a Specified Column or Columns&lt;/em&gt;, version 1.0.5, 2020, &lt;/a&gt;&lt;a href="https://csvkit.rtfd.org" role="doc-biblioref"&gt;https://csvkit.rtfd.org&lt;/a&gt;.&lt;/p&gt;'><sup>83</sup></a></span> to join the two data sets:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">csvjoin</span> -c species <span style="text-decoration: underline">iris.csv</span> <span style="text-decoration: underline">irismeta.csv</span> | <span style="color: #5f8700">csvcut</span> -c sepal_length,sepal_width,
species,usda_id | <span style="color: #5f8700">sed</span> -n <span style="color: #af8700">'1p;49,54p'</span> | <span style="color: #5f8700">csvlook</span>
│ sepal_length │ sepal_width │ species         │ usda_id │
├──────────────┼─────────────┼─────────────────┼─────────┤
│          4.6 │         3.2 │ Iris-setosa     │ IRSE    │
│          5.3 │         3.7 │ Iris-setosa     │ IRSE    │
│          5.0 │         3.3 │ Iris-setosa     │ IRSE    │
│          7.0 │         3.2 │ Iris-versicolor │ IRVE2   │
│          6.4 │         3.2 │ Iris-versicolor │ IRVE2   │
│          6.9 │         3.1 │ Iris-versicolor │ IRVE2   │</pre>
<p>Of course you can also use the SQL approach using <code>csvsql</code>, which is, as per usual, a bit longer (but potentially much more flexible):</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">csvsql</span> --query <span style="color: #af8700">'SELECT i.sepal_length, i.sepal_width, i.species, m.usda_id FRO
M iris i JOIN irismeta m ON (i.species = m.species)'</span> <span style="text-decoration: underline">iris.csv</span> <span style="text-decoration: underline">irismeta.csv</span> | <span style="color: #5f8700">sed
</span> -n <span style="color: #af8700">'1p;49,54p'</span> | <span style="color: #5f8700">csvlook</span>
│ sepal_length │ sepal_width │ species         │ usda_id │
├──────────────┼─────────────┼─────────────────┼─────────┤
│          4.6 │         3.2 │ Iris-setosa     │ IRSE    │
│          5.3 │         3.7 │ Iris-setosa     │ IRSE    │
│          5.0 │         3.3 │ Iris-setosa     │ IRSE    │
│          7.0 │         3.2 │ Iris-versicolor │ IRVE2   │
│          6.4 │         3.2 │ Iris-versicolor │ IRVE2   │
│          6.9 │         3.1 │ Iris-versicolor │ IRVE2   │</pre>
<!-- ## JSON -->
<!-- ## XML -->
<!-- ## HTML -->
</div>
</div>
</div>
<div id="working-with-xmlhtml-and-json" class="section level2" number="5.5">
<h2>
<span class="header-section-number">5.5</span> Working with XML/HTML and JSON<a class="anchor" aria-label="anchor" href="#working-with-xmlhtml-and-json"><i class="fas fa-link"></i></a>
</h2>
<p>In this section I’m going to demonstrate a couple of command-line tools that can convert data from one format to another.
There are two reasons to convert data.</p>
<p>First, oftentimes, the data needs to be in tabular form, just like a database table or a spreadsheet, because many visualization and machine learning algorithms depend on it.
CSV is inherently in tabular form, but JSON and HTML/XML data can have a deeply nested structure.</p>
<p>Second, many command-line tools, especially the classic ones such as <code>cut</code> and <code>grep</code>, operate on plain text.
This is because text is regarded as a universal interface between command-line tools.
Moreover, the other formats are younger. Each of these formats can be treated as plain text, allowing us to apply such command-line tools to the other formats as well.</p>
<p>Sometimes you can get away with applying the classic tools to structured data.
For example, by treating the JSON data below as plain text, you can change the attribute <em>gender</em> to <em>sex</em> using <code>sed</code>:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">sed</span> -e <span style="color: #af8700">'s/"gender":/"sex":/g'</span> <span style="text-decoration: underline">users.json</span> | <span style="color: #5f8700">jq</span> | <span style="color: #5f8700">trim</span>
{
  "results": [
    {
      "sex": "male",
      "name": {
        "title": "mr",
        "first": "leevi",
        "last": "kivisto"
      },
      "location": {
… with 260 more lines</pre>
<p>Like many other command-line tools, <code>sed</code> does not make use of the structure of the data.
Better to either use a tool that makes use of the structure of the data (such as <code>jq</code> which I discuss below), or first convert the data to a tabular format such as CSV and then apply the appropriate command-line tool.</p>
<p>I’m going to demonstrate converting XML/HTML and JSON to CSV through a real-world use case.
The command-line tools that I’ll be using here are: <code>curl</code>, <code>pup</code><span class="citation"><a class="footnote-ref" tabindex="0" data-toggle="popover" data-content='&lt;p&gt;&lt;a href="#ref-pup" role="doc-biblioref"&gt;Chiang, &lt;em&gt;&lt;span class="nocase"&gt;pup&lt;/span&gt; – Parsing HTML at the Command Line&lt;/em&gt;&lt;/a&gt;.&lt;/p&gt;'><sup>84</sup></a></span>, <code>xml2json</code><span class="citation"><a class="footnote-ref" tabindex="0" data-toggle="popover" data-content='&lt;p&gt;&lt;a href="#ref-xml2json" role="doc-biblioref"&gt;François Parmentier, &lt;em&gt;&lt;span class="nocase"&gt;xml2json&lt;/span&gt; – Convert an &lt;span&gt;XML&lt;/span&gt; Input to a &lt;span&gt;JSON&lt;/span&gt; Output, Using &lt;span class="nocase"&gt;xml-mapping&lt;/span&gt;&lt;/em&gt;, version 0.0.3, 2016, &lt;/a&gt;&lt;a href="https://github.com/parmentf/xml2json" role="doc-biblioref"&gt;https://github.com/parmentf/xml2json&lt;/a&gt;.&lt;/p&gt;'><sup>85</sup></a></span>, <code>jq</code> and <code>json2csv</code><span class="citation"><a class="footnote-ref" tabindex="0" data-toggle="popover" data-content='&lt;p&gt;&lt;a href="#ref-json2csv" role="doc-biblioref"&gt;Jehiah Czebotar, &lt;em&gt;&lt;span class="nocase"&gt;json2csv&lt;/span&gt; – Convert &lt;span&gt;JSON&lt;/span&gt; to &lt;span&gt;CSV&lt;/span&gt;&lt;/em&gt;, version 1.2.1, 2019, &lt;/a&gt;&lt;a href="https://github.com/jehiah/json2csv" role="doc-biblioref"&gt;https://github.com/jehiah/json2csv&lt;/a&gt;.&lt;/p&gt;'><sup>86</sup></a></span>.</p>
<p>Wikpedia holds a wealth of information. Much of this information is ordered in tables, which can be regarded as data sets.
For example, <a href="http://en.wikipedia.org/wiki/List_of_countries_and_territories_by_border/area_ratio">this page</a> contains a list of countries and territories together with their border length, their area, and the ratio between the two.</p>
<p>Let’s imagine that you’re interested in analyzing this data. In this section, I’ll walk you through all the necessary steps and their corresponding commands. I won’t go into every little detail, so it could be that you won’t understand everything right away. Don’t worry, I’re confident that you’ll get the gist of it. Remember that the purpose of this section is to demonstrate the command line. All tools and concepts used in this section (and more) will be explained in the subsequent chapters.</p>
<p>The data set that you’re interested in, is embedded in HTML.
Your goal is to end up with a representation of this data set that you can work with.
The very first step is to download the HTML using <code>curl</code>:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">curl</span> -sL <span style="color: #af8700">'http://en.wikipedia.org/wiki/List_of_countries_and_territories_by_bo
rder/area_ratio'</span> <span style="color: #af8700">&gt;</span> wiki.html</pre>
<p>The HTML is saved to a file named <em>wiki.html</em>.
Let’s see how the first 10 lines look like:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">wiki.html</span> <span style="color: #5f8700">trim</span>
&lt;!DOCTYPE html&gt;
&lt;html class="client-nojs" lang="en" dir="ltr"&gt;
&lt;head&gt;
&lt;meta charset="UTF-8"/&gt;
&lt;title&gt;List of countries and territories by border/area ratio - Wikipedia&lt;/titl…
&lt;script&gt;document.documentElement.className="client-js";RLCONF={"wgBreakFrames":…
"Lists of countries by geography","Lists by area","Border-related lists"],"wgPa…
"wgGENewcomerTasksGuidanceEnabled":true,"wgGEAskQuestionEnabled":false,"wgGELin…
"ext.cx.eventlogging.campaigns","ext.centralNotice.geoIP","ext.centralNotice.st…
&lt;script&gt;(RLQ=window.RLQ||[]).push(function(){mw.loader.implement("user.options@…
… with 3070 more lines</pre>
<p>That seems to be in order.
Image that you’ve been able to determine that the root HTML element that we’re interested in is a <em>&lt;table&gt;</em> with the class <em>wikitable</em>.
This allows you to look at the part that you’re interest in using <code>grep</code> (the <code>-A</code> option specifies the number of lines you want to print after the matching line):</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">grep</span> wikitable -A 21 <span style="text-decoration: underline">wiki.html</span>
&lt;table class="wikitable sortable"&gt;
&lt;tbody&gt;&lt;tr&gt;
&lt;th&gt;Rank&lt;/th&gt;
&lt;th&gt;Country or territory&lt;/th&gt;
&lt;th&gt;Total length of land borders (km)&lt;/th&gt;
&lt;th&gt;Total surface area (km&lt;sup&gt;2&lt;/sup&gt;)&lt;/th&gt;
&lt;th&gt;Border/area ratio (km/km&lt;sup&gt;2&lt;/sup&gt;)
&lt;/th&gt;&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1
&lt;/td&gt;
&lt;td&gt;Vatican City
&lt;/td&gt;
&lt;td&gt;3.2
&lt;/td&gt;
&lt;td&gt;0.44
&lt;/td&gt;
&lt;td&gt;7.2727273
&lt;/td&gt;&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;2
&lt;/td&gt;</pre>
<p>You now actually see the countries and their values.
The next step is to extract the necessary elements from the HTML file.
For this you can use <code>pup</code>:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">wiki.html</span> <span style="color: #5f8700">pup</span> <span style="color: #af8700">'table.wikitable tbody'</span> | <span style="color: #5f8700">tee</span> table.html | <span style="color: #5f8700">trim</span>
&lt;tbody&gt;
 &lt;tr&gt;
  &lt;th&gt;
   Rank
  &lt;/th&gt;
  &lt;th&gt;
   Country or territory
  &lt;/th&gt;
  &lt;th&gt;
   Total length of land borders (km)
… with 4199 more lines</pre>
<p>The expression passed to <code>pup</code> is a CSS-selector.
The syntax is usually used to style web pages, but you can also use it to select certain elements from HTML.
In this case, you want to select the <em>tbody</em> of the <em>table</em> which has the <em>wikitable</em> class.
Up next is <code>xml2json</code>, which converts XML (and HTML) to JSON.</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">table.html</span> <span style="color: #5f8700">xml2json</span> <span style="color: #af8700">&gt;</span> table.json
 
<span style="font-weight: bold">$</span> <span style="color: #5f8700">jq</span> <span style="text-decoration: underline">.</span> <span style="text-decoration: underline">table.json</span> | <span style="color: #5f8700">trim</span> 20
{
  "tbody": {
    "tr": [
      {
        "th": [
          {
            "$t": "Rank"
          },
          {
            "$t": "Country or territory"
          },
          {
            "$t": "Total length of land borders (km)"
          },
          {
            "$t": [
              "Total surface area (km",
              ")"
            ],
            "sup": {
… with 4691 more lines</pre>
<p>The reason I convert the HTML to JSON is because there is a very powerful tool called <code>jq</code> that operates on JSON data.
The following command extracts certain parts of the JSON data and reshapes it into a form that we can work with:</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #af8700">&lt;</span> <span style="text-decoration: underline">table.json</span> <span style="color: #5f8700">jq</span> -r <span style="color: #af8700">'.tbody.tr[1:][] | [.td[]["$t"]] | @csv'</span> | <span style="color: #5f8700">header</span> -a rank,c
ountry,border,surface,ratio <span style="color: #af8700">&gt;</span> countries.csv</pre>
<p>The data is now in a form that you can work with.
Those were quite a few steps to get from a Wikipedia page to a CSV data set.
However, when you combine all of the above commands into one, you will see that it’s actually really concise and expressive.</p>
<pre><span style="font-weight: bold">$</span> <span style="color: #5f8700">csvlook</span> --max-column-width 28 <span style="text-decoration: underline">countries.csv</span>
│ rank │ country                      │    border │       surface │  ratio │
├──────┼──────────────────────────────┼───────────┼───────────────┼────────┤
│    1 │ Vatican City                 │      3.20 │          0.44 │ 7.273… │
│    2 │ Monaco                       │      4.40 │          2.00 │ 2.200… │
│    3 │ San Marino                   │     39.00 │         61.00 │ 0.639… │
│    4 │ Liechtenstein                │     76.00 │        160.00 │ 0.465… │
│    5 │ Sint Maarten  (Netherlands)  │     10.20 │         34.00 │ 0.300… │
│    6 │ Andorra                      │    120.30 │        468.00 │ 0.257… │
│    7 │ Gibraltar  (United Kingdom)  │      1.20 │          6.00 │ 0.200… │
│    8 │ Saint Martin (France)        │     10.20 │         54.00 │ 0.189… │
… with 238 more lines</pre>
<p>That concludes the demonstration of conversion XML/HTML to JSON to CSV.
While <code>jq</code> can perform many more operations, and while there exist specialized tools to work with XML data, in my experience, converting the data to CSV format as quickly as possible tends to work well.
This way you can spend more time becoming proficient at generic command-line tools, rather than very specific tools.</p>
</div>
<div id="summary-4" class="section level2" number="5.6">
<h2>
<span class="header-section-number">5.6</span> Summary<a class="anchor" aria-label="anchor" href="#summary-4"><i class="fas fa-link"></i></a>
</h2>
<p>In this chapter we’ve looked at cleaning, or scrubbing, data.
As you’ve seen there is no single tool that can magically get rid of all the messiness of data; you’ll often need to combine multiple different tools to get the desired result.
Keep in mind that classic command-line tools such as <code>cut</code> and <code>sort</code> can’t interpret structured data.
Luckily, there are tools that convert one data format, such as JSON and XML, into another data format, such as CSV.
In the next chapter, which is again an intermezzo chapter, I’m going to show you how you can manage your project using <code>make</code>.
You’re free to skip this chapter if you can’t wait to start exploring and visualizing your data in <a href="chapter-7-exploring-data.html#chapter-7-exploring-data">Chapter 7</a>.</p>
</div>
<div id="for-further-exploration-4" class="section level2" number="5.7">
<h2>
<span class="header-section-number">5.7</span> For Further Exploration<a class="anchor" aria-label="anchor" href="#for-further-exploration-4"><i class="fas fa-link"></i></a>
</h2>
<ul>
<li>I wish I could’ve explained more about <code>awk</code>. It’s such a powerful tool and programming language. I highly recommend that you take the time to learn it. Two good resources are the book <em>sed &amp; awk</em> by Doherty and Robbins and the online <a href="https://www.gnu.org/software/gawk/manual/gawk.html">GNU Awk User’s Guide</a>.</li>
<li>In this chapter I have used regular expressions in a couple of places. A tutorial about them is unfortunately beyond the scope of this book. Because regular expressions can be used in many different tools, I recommend that you learn about them. A good book is <em>Regular Expressions Cookbook</em> by Jan Goyvaerts and Steven Levithan.</li>
</ul>
</div>
</div>

  <div class="chapter-nav">
<div class="prev"><a href="chapter-4-creating-command-line-tools.html"><span class="header-section-number">4</span> Creating Command-line Tools</a></div>
<div class="next"><a href="chapter-6-project-management-with-make.html"><span class="header-section-number">6</span> Project Management with Make</a></div>
</div></main><div class="col-md-3 col-lg-3 d-none d-md-block sidebar sidebar-chapter">
    <nav id="toc" data-toggle="toc" aria-label="On this page"><h2>On this page</h2>
      <ul class="nav navbar-nav">
<li><a class="nav-link" href="#chapter-5-scrubbing-data"><span class="header-section-number">5</span> Scrubbing Data</a></li>
<li><a class="nav-link" href="#overview-2"><span class="header-section-number">5.1</span> Overview</a></li>
<li><a class="nav-link" href="#transformations-transformations-everywhere"><span class="header-section-number">5.2</span> Transformations, Transformations Everywhere</a></li>
<li>
<a class="nav-link" href="#plain-text"><span class="header-section-number">5.3</span> Plain Text</a><ul class="nav navbar-nav">
<li><a class="nav-link" href="#filtering-lines"><span class="header-section-number">5.3.1</span> Filtering Lines</a></li>
<li><a class="nav-link" href="#extracting-values"><span class="header-section-number">5.3.2</span> Extracting Values</a></li>
<li><a class="nav-link" href="#replacing-and-deleting-values"><span class="header-section-number">5.3.3</span> Replacing and Deleting Values</a></li>
</ul>
</li>
<li>
<a class="nav-link" href="#csv"><span class="header-section-number">5.4</span> CSV</a><ul class="nav navbar-nav">
<li><a class="nav-link" href="#bodies-and-headers-and-columns-oh-my"><span class="header-section-number">5.4.1</span> Bodies and Headers and Columns, Oh My!</a></li>
<li><a class="nav-link" href="#performing-sql-queries-on-csv"><span class="header-section-number">5.4.2</span> Performing SQL Queries on CSV</a></li>
<li><a class="nav-link" href="#extracting-and-reordering-columns"><span class="header-section-number">5.4.3</span> Extracting and Reordering Columns</a></li>
<li><a class="nav-link" href="#filtering-rows"><span class="header-section-number">5.4.4</span> Filtering Rows</a></li>
<li><a class="nav-link" href="#merging-columns"><span class="header-section-number">5.4.5</span> Merging Columns</a></li>
<li><a class="nav-link" href="#combining-multiple-csv-files"><span class="header-section-number">5.4.6</span> Combining Multiple CSV Files</a></li>
</ul>
</li>
<li><a class="nav-link" href="#working-with-xmlhtml-and-json"><span class="header-section-number">5.5</span> Working with XML/HTML and JSON</a></li>
<li><a class="nav-link" href="#summary-4"><span class="header-section-number">5.6</span> Summary</a></li>
<li><a class="nav-link" href="#for-further-exploration-4"><span class="header-section-number">5.7</span> For Further Exploration</a></li>
</ul>

      <div class="book-extra">
        <ul class="list-unstyled">
<li><a id="book-source" href="https://github.com/jeroenjanssens/data-science-at-the-command-line/blob/master/book/2e/05.Rmd">View source <i class=""></i></a></li>
          <li><a id="book-edit" href="https://github.com/jeroenjanssens/data-science-at-the-command-line/edit/master/book/2e/05.Rmd">Edit this page <i class=""></i></a></li>
        </ul>
</div>
    </nav>
</div>

</div>
</div> <!-- .container -->

<footer class="bg-primary text-light mt-5"><div class="container-fluid">
    <div class="row">
      <div class="d-none d-lg-block col-lg-2 sidebar"></div>
      <div class="col-sm-12 col-md-9 col-lg-7 mt-3" style="max-width: 45rem;">
        <p><strong>Data Science at the Command Line, 2e</strong> by <a href="https://twitter.com/jeroenhjanssens" class="text-light">Jeroen Janssens</a>. Updated on December 14, 2021. This book was built by the <a class="text-light" href="https://bookdown.org">bookdown</a> R package.</p>
      </div>
      <div class="col-md-3 col-lg-3 d-none d-md-block sidebar"></div>
    </div>
  </div>
</footer>
</body>
</html>
